ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How do I add a formula to a macro? (https://www.excelbanter.com/excel-discussion-misc-queries/195975-how-do-i-add-formula-macro.html)

Aline
How do I add a formula to a macro?
 
Could anyone helping me with this?

After deleting column A & B, adding 2 column G and H and enter text in G2
and H2, Id 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()

--
Thanks,
Aline

Dave Peterson
How do I add a formula to a macro?
 
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????

Aline wrote:

Could anyone helping me with this?

After deleting column A & B, adding 2 column G and H and enter text in G2
and H2, Id 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()

--
Thanks,
Aline

--

Dave Peterson

Aline
How do I add a formula to a macro?
 
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" wrote:

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????

Aline wrote:

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()

--
Thanks,
Aline

--

Dave Peterson

Aline
How do I add a formula to a 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
--
Aline


"Aline" wrote:

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" wrote:

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????

Aline wrote:

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()

--
Thanks,
Aline

--

Dave Peterson

Dave Peterson
How do I add a formula to a macro?
 
Or just format columns G:H as General (manually or in the macro).

Aline wrote:

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" wrote:

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" wrote:

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????

Aline wrote:

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()

--
Thanks,
Aline

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 04:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com