VBA Macro Loop
 

I am trying to create a loop in VBA that will recognize cells in column A by
the word "Totals" and cut that row and paste into a new worksheet. I am
assuming an If statement with a Loop and some sort of count feature. I am a
VBA newbie so any help is appreciated. Here is an example of the data I am
using. Keep in mind we will have up to 25 GL_Date "Totals".

GL_Date Credits
11/01/2004 1087471
11/01/2004 342796.36
11/01/2004 426693.76
11/01/2004 260553.9
11/01/2004 22456
11/01/2004 5517.41
11/01/2004 211887.43
11/01/2004 846.25
11/01/2004 4808.83
11/01/2004 6718.39
11/01/2004 22500
11/01/2004 291256.92
11/01/2004 Total 2683506.25

VBA Macro Loop
 

Hi

Try

Sub test()
Dim R As Long, L As Long
R = 1
Do
If InStr(Sheets(1).Cells(R, 1).Value, "Total") 0 Then
L = L + 1
Sheets(2).Cells(L, 1).Value = Sheets(1).Cells(R, 1).Value
End If
R = R + 1
Loop Until Sheets(1).Cells(R, 1).Value = ""
End Sub

You say "column A" but it may look like the Total word is in B ? If so try
this instead:

Sub test()
Dim R As Long, L As Long
R = 1
Do
If InStr(Sheets(1).Cells(R, 2).Value, "Total") 0 Then
L = L + 1
Sheets(2).Cells(L, 1).Value = Sheets(1).Cells(R, 1).Value
Sheets(2).Cells(L, 2).Value = Sheets(1).Cells(R, 2).Value
End If
R = R + 1
Loop Until Sheets(1).Cells(R, 1).Value = ""
End Sub

HTH. best wishes Harald


"Neutron1871" skrev i melding
...
I am trying to create a loop in VBA that will recognize cells in column A
by
the word "Totals" and cut that row and paste into a new worksheet. I am
assuming an If statement with a Loop and some sort of count feature. I am
a
VBA newbie so any help is appreciated. Here is an example of the data I
am
using. Keep in mind we will have up to 25 GL_Date "Totals".

GL_Date Credits
11/01/2004 1087471
11/01/2004 342796.36
11/01/2004 426693.76
11/01/2004 260553.9
11/01/2004 22456
11/01/2004 5517.41
11/01/2004 211887.43
11/01/2004 846.25
11/01/2004 4808.83
11/01/2004 6718.39
11/01/2004 22500
11/01/2004 291256.92
11/01/2004 Total 2683506.25