Autfill a formula in VBA

[John Corbin]

Hi

I have a need to apply a formula to cells in a column using VBA

The autofill starts at row 2 of column I on a sheet called "working
data" and goes until the last cell used in column I, so I also need to
know what the last cell used in column I is.

The formula is pretty complex.

=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)),
100))),"UNKNOWN",
IF(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)),100))<999,
TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)),100)),"UNKNOWN"))

Thanks in advance

 

[Dave Peterson]

How about using column H to determine the last cell that should be used in
column I?

Option Explicit
Sub testme()

Dim myFormula As String
Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Working Data")

myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))),""UNKNOWN""," _
& "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))<999," _
& "TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100)),""UNKNOWN""))"

With wks
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Range("i2:i" & LastRow).Formula = myFormula
End With

End Sub

Notice that the double quotes are doubled up inside that string.

 

[John Corbin]

How about using column H to determine the last cell that should be used in
column I?

Option Explicit
Sub testme()

    Dim myFormula As String
    Dim wks As Worksheet
    Dim LastRow As Long

    Set wks = Worksheets("Working Data")

    myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
                & "REPT("" "",100)),100))),""UNKNOWN""," _
                & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
                & "REPT("" "",100)),100))<999," _
                & "TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
                & "REPT("" "",100)),100)),""UNKNOWN""))"

    With wks
        LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        .Range("i2:i" & LastRow).Formula = myFormula
    End With

End Sub

Notice that the double quotes are doubled up inside that string.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the prompt reply

I coppied it exactly as shown but althouth it autfilled teh formula in
Column I, it did not insert a coluimn first between cloumns H and I..
I just noticed taht I neglected to mention that...

and alstly..

how can I assign thsi code to a button that sits on sheet "Controls"

 

[IanC]

Another alternative. Watch for the line wrapping. Anything that starts in
the first column belongs with the previous line.

Sub InsertFormula()
Range("I1").EntireColumn.Insert
lNum = Range("J65536").End(xlUp).Row ' Finds last row in column J
(was I)
For rownum = 2 To lNum
Range("I" & rownum).Formula =
"=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H" & rownum & ",""/"",REPT(""
"",100)),100))),""UNKNOWN"",IF(--TRIM(RIGHT(SUBSTITUTE(H" & rownum &
",""/"",REPT("" "",100)),100))<999,TRIM(RIGHT(SUBSTITUTE(H" & rownum &
",""/"",REPT("" "",100)),100)),""UNKNOWN""))"
Next
End Sub

The first line (after the Sub row) inserts new column between H & I. The
second line finds the last used row in column J (was column I before the new
column was inserted. The For...Next loop inserts the formula in the new
column I, starting at row 2. I've assumes that you want I2 to refer to H2,
I3 to H3 etc. If this isn't the case, replace " & rownum & " (including the
quotes) with 2.

--
Ian
--

How about using column H to determine the last cell that should be used in
column I?

Option Explicit
Sub testme()

Dim myFormula As String
Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Working Data")

myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))),""UNKNOWN""," _
& "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))<999," _
& "TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100)),""UNKNOWN""))"

With wks
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Range("i2:i" & LastRow).Formula = myFormula
End With

End Sub

Notice that the double quotes are doubled up inside that string.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the prompt reply

I coppied it exactly as shown but althouth it autfilled teh formula in
Column I, it did not insert a coluimn first between cloumns H and I..
I just noticed taht I neglected to mention that...

and alstly..

how can I assign thsi code to a button that sits on sheet "Controls"

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myFormula As String
Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Working Data")

myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))),""UNKNOWN""," _
& "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))<999," _
& "TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100)),""UNKNOWN""))"

With wks
.range("i1").entirecolumn.insert
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Range("i2:i" & LastRow).Formula = myFormula
End With

End Sub

You can add a button from the Forms toolbar to that worksheet. When you do
that, you'll be prompted to assign a macro to that button. Choose TestMe.
(Actually, I'd rename this subroutine and use it instead!)