MS Excel 2002 Nightmare

  JoJoJo 15:35 08 Feb 2003

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.


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 2003

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 2003

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.


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


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.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?