IF/THEN in Macro

[Jackdaddy0711]

here's my predicament: Sheet 2, RowH4-FS4 contains 1 of 2 possible words: Self-Perform & Sub-Contract. I have created a macro to move all data from Sheet 1 to Sheet 4 in a vertical format. Sheet1, H4-Fs4 needs to be moved to Sheet4 column E or F (Self-Perform or Sub-Contract). The macro worked fine until I attempted the IF/THEN statement. I believe it has to do with the End If placement. Any help is appreciated... Macro below.

Thank you,

Please Login or Register  to view this content.

[stojko89]

your Ifs and thens are at the right place.
When you get the error and you hit debug which line does it color?

[wallyeye]

You probably should be a bit more explicit with your range/cell references. Here, you didn't put the "." before the range reference on your two if statements. The With statement is ok, but I would explicitly point to the other worksheet as well:

Please Login or Register  to view this content.

Doing this means you won't have to actually select Sheet2. I would also recommend not using Sheets(2) and Sheets(4). Typically the tab names are static even if they get moved around, you could refer to them by name: Sheets("Sheet2") and Sheets("Sheet4") - of course changing to your actual names. An even safer approach would be to rename them in the VBA IDE's Properties (select View Properties, select each sheet and change the name in the Properties window). Doing that would allow the programmer (you) to control the sheets very tightly. Typically when I do this, I would name them in Hungarian notation, like wksSheet2. Then, in your code:

Please Login or Register  to view this content.

and so on.

[Jackdaddy0711]

Stojko89: I never got a debug highlight

[Jackdaddy0711]

Thanks Wallyeye. All your fabulous effort still didn't work. I'm stumped. All the other columns (A-D) populate perfectly. Would you consider making a 2nd Macro for the IF/THEN goal?

[wallyeye]

You might try using the VBA IDE's Immediate window (alt-G) to see what exactly is going on. If you put a breakpoint on your first If statement (select the line and press F9), you can do this in the immediate window:

?Sheets(2).Range("E" & LR2).value
?.Range("E" & LR2).Value
?Sheets(2).Cells(1, c.colum).Value
?Sheets(2).Range("F" & LR2).value
?.Range("F" & LR2).Value
?Sheets(2).Cells(1, c.colum).Value

Just copy these lines to the immediate window and press enter on each one to see the current values.

lol, I just noticed c.colum is one character short, c.column will work better! Also, I missed a period on the second If statement, between Sheets(2) and Range.

[protonLeah]

With Sheets(4)
LR2 = .Range("A69964").End(xlUp).Row + 1
....
If Sheets(2).Range("E" & LR2).Value = "Sub-Contract" Then ...?

[Jackdaddy0711]

Wallyeye,

I haven't had a chance to try that, just wanted to say thanks. I'll let you know.

JD

[Jackdaddy0711]

Wallyeye,

I am WAY beyond the scope of my abilities. Below are the errors I get while viewing values in Immediate Window:

?Sheets(2).Range("E" & LR2).value RUN TIME ERROR 1004 Application-Defined or Object-Defined Error
?.Range("E" & LR2).Value COMPILE ERROR - Invalid watch expression
?Sheets(2).Cells(1, c.colum).Value RUN TIME ERROR 424 - Object required
?Sheets(2).Range("F" & LR2).value RUN TIME ERROR 1004 Application-Defined or Object-Defined Error
?.Range("F" & LR2).Value COMPILE ERROR - Invalid watch expression
?Sheets(2).Cells(1, c.colum).Value RUN TIME ERROR 424 - Object required

I never thought IF/THEN would be such a daunting task in a macro!

[wallyeye]

Did you try fixing up colum ---> column ? and Sheets(2)Range ---> Sheets(2).Range ?

What is the value of LR2?

?LR2
?range("E" & LR2).address

It is probably whining because it doesn't like the dot references in the immediate window.

[Jackdaddy0711]

I did fix the column typos & still end up with same Compile & Run Time Errors

LR2 = .Range("A69964").End(xlUp).Row + 1

[wallyeye]

I'm not sure where the problem is, can you post a dummy workbook?