Copy down formula to last row - VBA for BEGINNER

[mstod]

Hello all,

I am a beginner (with a capital B) to macros and VBA. I am recording a macro and trying to insert an IF formula into F2, and then fill the formula down to the last row of data. I think I can use Column A to determine my last row. I've read a few threads about this, but need help understanding how to insert the code into my macro. Can someone help?

The formula I'm using is =IF(F2>0, "YES", ""), where if the value in F2 is greater than 0, it should change to text "YES" or stay blank if the condition is not met.

Thank you!

 

[Norie]

Perhaps something like this.

Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("G2:G" & LastRow).Formula = "=F2>0, ""YES"", """")"

Note I've used column G for the formula as if you put it in column F it would create a whole bunch of circular references.

 

[JoeMo]

Hello all,

I am a beginner (with a capital B) to macros and VBA. I am recording a macro and trying to insert an IF formula into F2, and then fill the formula down to the last row of data. I think I can use Column A to determine my last row. I've read a few threads about this, but need help understanding how to insert the code into my macro. Can someone help?

The formula I'm using is =IF(F2>0, "YES", ""), where if the value in F2 is greater than 0, it should change to text "YES" or stay blank if the condition is not met.

Thank you!

If you put that formula in F2, it will create a circular reference. Where do you actually want to put that formula? Say you want it in G2, then you don't need to fill down, you can insert the formula in all relevant cells like this:

Range("G2:G" & Cells(rows.count,"F").end(xlup).Row).Formula = "=IF(F2>0, ""YES"", """")"

 

[mstod]

Thanks for catching that, to both of you. After posting this, I created the first part of my macro and the columns in question will actually be K and L. But to you point, I avoided that by copying the inserting a copied column next to it so that the formula was actually in G2, and referencing F2 (or K2, referencing L2 in my case now). Then I copied down the formula (which obviously won't work in a macro when I use spreadsheets with more data rows until I learn more from this thread), and then copy/pasted values. Then, I deleted L2. I am sure there is a much more efficient way of doing this, but as I said, I am a beginner!

Norie - do I insert that code at the bottom, or right after the action?

JoeMo, I tried using your formula, but it didn't take it. You meant that I would put this as a formula, within cell K2? Or, would it have to go in as code when I step into the macro?

 

[JoeMo]

Thanks for catching that, to both of you.

JoeMo, I tried using your formula, but it didn't take it. You meant that I would put this as a formula, within cell K2? Or, would it have to go in as code when I step into the macro?

That's not a formula you enter in the worksheet. It's a line of VBA code that will enter the formula for you, starting in G2, and negate the need to fill down col G for as far down as you have data in col F.

 

[mstod]

Hi JoeMo,

Thanks for your help - I've gotten it figured out for the most part. I used that method, but after running the macro I kept getting the formula as text in just the first column of the set. I gave up trying to figure it out at the end of the day, then came back to it today and somehow got it to run correctly. Now, I have tried the same method on another set of columns using an If(OR) formula, but ran into the same problem with both of the new columns (original ones are still calculating correctly after running the macro). Some forums suggest this is because the columns are formatted as text, not "general". Do you have a VBA code for changing the two columns (S and T) from Text to General? Would that solve the problem, do you think?

For your reference, the VBA code for the formula being used here is:

Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(OR(U2=""Y"",V2=""Y""), ""X"", """")"
Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(W2=""Y""), ""X"", """")"

Thanks.

 

[JoeMo]

Hi JoeMo,

Thanks for your help - I've gotten it figured out for the most part. I used that method, but after running the macro I kept getting the formula as text in just the first column of the set. I gave up trying to figure it out at the end of the day, then came back to it today and somehow got it to run correctly. Now, I have tried the same method on another set of columns using an If(OR) formula, but ran into the same problem with both of the new columns (original ones are still calculating correctly after running the macro). Some forums suggest this is because the columns are formatted as text, not "general". Do you have a VBA code for changing the two columns (S and T) from Text to General? Would that solve the problem, do you think?

For your reference, the VBA code for the formula being used here is:

Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(OR(U2=""Y"",V2=""Y""), ""X"", """")"
Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(W2=""Y""), ""X"", """")"

Thanks.

What's in columns S & T before you run the code to put the formulas in them? Are columns U & V formatted as Text?

 

[JoeMo]

What's in columns S & T before you run the code to put the formulas in them? Are columns U & V formatted as Text?

Ignore my questions, this may be easier. Replace those two formula lines with the code below.

With Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row)
      .Formula ="=IF(OR(U2=""Y"",V2=""Y""), ""X"", """")"
      .TextToColumns .Cells
End With
With Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Formula = "=IF(W2=""Y""), ""X"", """")"
    .TextToColumns .Cells
End With

 

[mstod]

Thanks, JoeMo. When I run the macro after making your edit, it gives me the error towards the end "There's already data here, do you want to replace it?" and then if I click yes, the formula still appears but only part of it.

Column S shows:
=IF(OR(U2="Y",V2="Y"),

and Column T shows:
=IF(W2="Y"),

If I click cancel instead of yes, I get run-time error '1004: TextToColumns method of range class failed.

To try to answer your original question, columns S & T are newly created columns (blank), and U, V and W are formatted as text. I did try to reformat them to General before running the macro, but it didn't change the outcome.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 324"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(OR(U2="Y",V2="Y"),[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[JoeMo]

Thanks, JoeMo. When I run the macro after making your edit, it gives me the error towards the end "There's already data here, do you want to replace it?" and then if I click yes, the formula still appears but only part of it.

Column S shows:
=IF(OR(U2="Y",V2="Y"),

and Column T shows:
=IF(W2="Y"),

If I click cancel instead of yes, I get run-time error '1004: TextToColumns method of range class failed.

To try to answer your original question, columns S & T are newly created columns (blank), and U, V and W are formatted as text. I did try to reformat them to General before running the macro, but it didn't change the outcome.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 324"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(OR(U2="Y",V2="Y"),[/TD]
[/TR]
</tbody>[/TABLE]

Looks like your second formula has an extra ")" in it.
This revision works for me:

With Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row)
      .Formula = "=IF(OR(U2=""Y"",V2=""Y""), ""X"", """")"
      .TextToColumns .Cells
End With
With Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Formula = "=IF(W2=""Y"",""X"","""")"
    .TextToColumns .Cells
End With