MS Excel 2002 Nightmare

  JoJoJo 15:35 08 Feb 03
Locked

I am using MS Excel 2002. I have a sheet which contains some values that I want to transfer to another sheet using Macros. However, it’s not quite that that straight forward because the location of the data that gets pasted on to the second sheet depends what the data is!

On the second sheet (the one which receives the data) column A contains a list of names and row one a list of months. The first sheet, which will contain the data to be copied has a month in cell E2 and a name in cell E1. There is also a value in cell E3. What I want the Macro (or other Excel features as you consider appropriate) to do is copy the value from E3 to the row of sheet 2 which has the same name in column A as appears in E1 on the first sheet, but in the column which corresponds with to the month in E2 of the first sheet.

I apologise that this sounds really complicated and have attempted to explain it as clearly as possible. Any advice would be greatly appreciated.

Joe

P.S. The month column into which the value must be pasted on sheet 2 will normally be the next available cell (i.e. When January is occupied the next pasting will be to February) if this makes things any clearer…?!

  VoG™ 18:12 08 Feb 03

It does sound complicated and I would prefer to avoid a lot of potentially wasted effort. I can think of (at least) two ways of doing this but I would like to see the workbook layout for myself.

I will e-mail you. If the workbook is not commercially sensitive, reply attaching a copy of the file. Then I'll see what I can do.

  VoG™ 11:08 09 Feb 03

Here is a macro that will accomplish this. It is not foolproof as there is no check that the matching values for name and month are actually found. It may come in useful to somebody searching the site.

ALT+F11 to open the Visual Basic Editor.

Insert/Module

Paste in the following code:

Sub Transfer()

Dim Lastrow As Integer, iRow As Integer, iCol As Integer

Dim Mth As String, Nme As String, X

Dim Insheet As Worksheet, Outsheet As Worksheet


Set Insheet = Sheets("VoG")

Set Outsheet = Sheets("Menu")


Nme = Insheet.Range("E1").Value

Mth = Insheet.Range("E2").Value

X = Insheet.Range("E3").Value


Outsheet.Activate


Lastrow = Cells(Cells.Rows.Count, 1).End(xlUp).Row


For iRow = 1 To Lastrow

If UCase(Outsheet.Range("A" & iRow).Value) = UCase(Nme) Then Exit For

Next iRow


For iCol = 2 To 13

If UCase(Outsheet.Cells(4, iCol).Value) = UCase(Mth) Then Exit For

Next iCol


Outsheet.Cells(iRow, iCol).Value = X

End Sub



ALT+F11 to exit the VBE. Tools/Macro/Macros, select Transfer and click Run.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…