How do I add a formula to a macro?

  • Thread starter Thread starter Aline
  • Start date Start date
A

Aline

Could anyone helping me with this?

After deleting column A & B, adding 2 column G and H and enter text in G2
and H2, I’d like to add a formula G2 (as well as H2). How do I add formula
to a macro?

Formula on G2: =IF(I2="0001", "E?", IF(OR(I2="0002", I2="0003",
I2="0004", I2="0005"), "Y", "N"))
Formula on H2: =IF(A2<>A3,"Row used")

As following is the VBA codes for my macro:
Sub CompFormula()
'
' CompFormula Macro

Columns("H:H").ColumnWidth = 12.14
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "Meet Y/N"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Row used"
Range("G2").Select
End SubSub CompFormula()
 
I'm guessing that you really want to fill a range of cells in G and H with these
formulas--not just G2 and H2.

If that's true, can you pick out a column that could be used to find the last
row to use?

I used column A in this example:

Option Explicit
Sub testme01()

Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & LastRow).Formula _
= "=IF(I2=""0001"", ""E?"", IF(OR(I2=""0002""," _
& "I2=""0003"", I2=""0004"", I2=""0005""), ""Y"", ""N""))"
.Range("H2:H" & LastRow).Formula = "=IF(A2<>A3,""Row used"")"
End With
End Sub

And your values are really text--not numbers formatted to have leading 0's????
 
Hi Dave,

Thank you for your response. Yes what I wanted was to fill a range a cells
in G and H with the same formula. I did not know that we could do that with
macro.

I put your code in the macro, and it did try to input formula from the first
row to the last row but the problem was that the cells on the column G show
up as =IF(I2="0001", "E?", IF(OR(I2="0002",I2="0003", I2="0004", I2="0005"),
"Y", "N")) instead of Y or N, and the cells on the column H show as
=IF(A2<>A3,"Row used") instead of Row used or False.

Would you be able to help me again?

Here is the code :
Sub try()
'
' try Macro
'

'
Columns("H:H").ColumnWidth = 12.14
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "Meet Y/N"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Row used"
Range("G2").Select


Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & LastRow).Formula _
= "=IF(I2=""0001"", ""E?"", IF(OR(I2=""0002""," _
& "I2=""0003"", I2=""0004"", I2=""0005""), ""Y"", ""N""))"
.Range("H2:H" & LastRow).Formula = "=IF(A2<>A3,""Row used"")"

End With
'
End Sub

--
Thanks,
Aline


Dave Peterson said:
I'm guessing that you really want to fill a range of cells in G and H with these
formulas--not just G2 and H2.

If that's true, can you pick out a column that could be used to find the last
row to use?

I used column A in this example:

Option Explicit
Sub testme01()

Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & LastRow).Formula _
= "=IF(I2=""0001"", ""E?"", IF(OR(I2=""0002""," _
& "I2=""0003"", I2=""0004"", I2=""0005""), ""Y"", ""N""))"
.Range("H2:H" & LastRow).Formula = "=IF(A2<>A3,""Row used"")"
End With
End Sub

And your values are really text--not numbers formatted to have leading 0's????
Click to expand...
 
Hi Dave,

That's fine, Dave. I've figured it out. It's because the column G and H
are text column not gerneral column. I have simply put the the formula uder
the other columns that are general column.

Thanks again for your help.
Aline
--
Aline


Aline said:
Hi Dave,

Thank you for your response. Yes what I wanted was to fill a range a cells
in G and H with the same formula. I did not know that we could do that with
macro.

I put your code in the macro, and it did try to input formula from the first
row to the last row but the problem was that the cells on the column G show
up as =IF(I2="0001", "E?", IF(OR(I2="0002",I2="0003", I2="0004", I2="0005"),
"Y", "N")) instead of Y or N, and the cells on the column H show as
=IF(A2<>A3,"Row used") instead of Row used or False.

Would you be able to help me again?

Here is the code :
Sub try()
'
' try Macro
'

'
Columns("H:H").ColumnWidth = 12.14
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "Meet Y/N"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Row used"
Range("G2").Select


Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & LastRow).Formula _
= "=IF(I2=""0001"", ""E?"", IF(OR(I2=""0002""," _
& "I2=""0003"", I2=""0004"", I2=""0005""), ""Y"", ""N""))"
.Range("H2:H" & LastRow).Formula = "=IF(A2<>A3,""Row used"")"

End With
'
End Sub
Click to expand...
 
Or just format columns G:H as General (manually or in the macro).
Hi Dave,

That's fine, Dave. I've figured it out. It's because the column G and H
are text column not gerneral column. I have simply put the the formula uder
the other columns that are general column.

Thanks again for your help.
Aline
Click to expand...
 
Back
Top