CODEWITHSUNDEEP
vbaexcel
mb1987
mb1987

Reputation: 467

Change in macro formula

I have an Excel file that looks like:

enter image description here

and I want to make it look like this:

enter image description here

The code I am using is:

   Sub FindString()
   Dim A As Range, r As Range, last As Range
   Set A = Intersect(ActiveSheet.UsedRange, Range("A:A"))

   For Each r In A
       If IsNumeric(Left(r, 6)) Then Set last = r
       If Not last Is Nothing Then last.Copy r.Offset(0, 1)
  Next r
 End Sub

It works but if the rows are more than 50,000 will hang Excel. So I found some help on this site and changed to:

Sub Demo()
Dim r As Range

With ActiveSheet
    Set r = .Range(.Cells(4, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

r.Offset(0, 1).Formula = "=IF(ISNUMBER(VALUE(LEFT(A4,6))),A4,B3)"
r.Offset(0, 1) = r.Offset(0, 1).Value

End Sub

Now the trouble is I don't understand what this bit: 

"=IF(ISNUMBER(VALUE(LEFT(A4,6))),A4,B3)"

in the formula is doing and if in my current formula if I change to if condition: 

  If IsNumeric(Left(r, 6)) And 0 = InStr(r, "Totals:")

How can I change the similar to sub DEMO() ?

Upvotes: 2

Views: 81

Answers (2)

u8it
u8it

Reputation: 4296

If you want faster speed, use something like this instead of the demo code you posted. This code is also more flexible. The only

Sub Demo2()
    Application.ScreenUpdating = False
    Dim A() As Variant
    A = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value2
    Dim B() As Variant
    ReDim B(1 To UBound(A), 1 To 1)
    Dim ID As String
    For i = 1 To UBound(A)
        If IsNumeric(Left(A(i, 1), 1)) Then
            ID = A(i, 1)
            'If you want to use a dynamic equation, use the below line instead
            'ID = "=A" & i
        End If
        B(i, 1) = ID
    Next
    Range("B1:B" & UBound(B)) = B
End Sub

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

=IF(ISNUMBER(VALUE(LEFT(A4,6))),A4,B3) is checking the cell in column A. If this cell starts with four numbers, then return the value in A4, otherwise, repeat the data from the cell above.

Can you clarify the second question? If IsNumeric(Left(r, 6)) is the same in VBA as the formula in Excel. The second part (0 = InStr...) is checking to see if the cell has "Totals:" inside it. If it does not, then continue on with the if statement (if your cell does have "Totals:", then 1 will be returned, and the If statement won't continue).

Upvotes: 0

Related Questions