Apply formulas to cells in range with VBA with increasing row values to match?

[LucidCheetah]

I need to assign a formula to a range of cells in VBA to fix an issue I'm having. In the range of A1:A300 I have the formula =D1&"_"&E1, =D2&"_"&E2, =D3&"_"&E3, etc etc increasing to match the row number through the column range. How can I apply this formula to each cell in the range with the E & D row values increasing to match the row value of the cells in range?

 

[juddaaaa]

Try this

Sub InsertFormulas()

    Dim Cel As Range
    
    For Each Cel In Range("A1:A300")
        With Cel
            .Formula = "=D" & .Row & "&""_""&E" & .Row
        End With
    Next Cel

End Sub

 

[Rick Rothstein]

You can do what you want without using a loop...

Sub InsertFormulas()
  Range("A1:A300").Formula = "=D1&""_""&E1"
End Sub

 

[LucidCheetah]

Try this

Sub InsertFormulas()

    Dim Cel As Range
   
    For Each Cel In Range("A1:A300")
        With Cel
            .Formula = "=D" & .Row & "&""_""&E" & .Row
        End With
    Next Cel

End Sub

Thanks!

 

[LucidCheetah]

You can do what you want without using a loop...

Sub InsertFormulas()
  Range("A1:A300").Formula = "=D1&""_""&E1"
End Sub

This seems to fit into my macro a little easier and works for 2 out of 3 on the formulas I'm trying to apply it to. The third formula doesn't seem to want to work for me though. When i put the formulas in manually it gives me all the data I want, however trying to apply this in VBA gives me a Syntax error? Am i missing something, or is there an easier way to achieve what I'm trying to do here?

Worksheets("Sheet2").Range("C2:C300").Formula = "=IF(L2="DOWN","D",IF(L2="TOP","T",IF(L2="SIDE","S",IF(L2="HIGH","H",""))))"

 

[juddaaaa]

Glad to help.

Rick's code would probably be a better way to do it.

 

[Rick Rothstein]

This seems to fit into my macro a little easier and works for 2 out of 3 on the formulas I'm trying to apply it to. The third formula doesn't seem to want to work for me though. When i put the formulas in manually it gives me all the data I want, however trying to apply this in VBA gives me a Syntax error? Am i missing something, or is there an easier way to achieve what I'm trying to do here?

Worksheets("Sheet2").Range("C2:C300").Formula = "=IF(L2="DOWN","D",IF(L2="TOP","T",IF(L2="SIDE","S",IF(L2="HIGH","H",""))))"

Text constants (what is being assigned to the Formula property) have quote marks on the outside ends. So that VB can tell what is a quote mark delineating a text constant and what is an actual quote mark character located inside the quote marks delineating a text constant, VB requires those actual (internal) quote marks to be doubled up. Here is your code line with that doubling up of internal quote marks applied....

Worksheets("Sheet2").Range("C2:C300").Formula = "=IF(L2=""DOWN"",""D"",IF(L2=""TOP"",""T"",IF(L2=""SIDE"",""S"",IF(L2=""HIGH"",""H"",""""))))"

 

[juddaaaa]

When you put quotes inside of quotes you need to double up

Worksheets("Sheet2").Range("C2:C300").Formula = "=IF(L2=""DOWN"",""D"",IF(L2=""TOP"",""T"",IF(L2=""SIDE"",""S"",IF(L2=""HIGH"",""H"",""""))))"

 

[juddaaaa]

Beat me to it

 

[LucidCheetah]

Thanks a lot Rick. That seems to have solved days of problems for me!