Looking for some VBA help to automate this task. Basically I'm trying to
create a columnar list of data that includes the account number in column A
from a spreadsheet that displays the data in account number "groups"

1. For each instance of the text “SH” in column B, write the contents of
the cell in that row from column E into the same row in column A
2. Copy the value just written into column A down to the row directly above
the next instance of “SH” in column B
3. Repeat through entire spreadsheet.

Kirk
I think i could help if I could see the sheet before and what you want it to
look like after. If you don't mind sending the workbook to me at
(E-Mail Removed). I will take a look at and see what I can do.

"Kirk P." wrote:

> Looking for some VBA help to automate this task. Basically I'm trying to
> create a columnar list of data that includes the account number in column A
> from a spreadsheet that displays the data in account number "groups"
>
> 1. For each instance of the text “SH” in column B, write the contents of
> the cell in that row from column E into the same row in column A
> 2. Copy the value just written into column A down to the row directly above
> the next instance of “SH” in column B
> 3. Repeat through entire spreadsheet.
>

Sub UpdateGroups()

Set c = Columns("B").Find(what:="SH", _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ""SH""! Exiting Macro")
Exit Sub
Else
FirstRow = c.Row
End If

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For RowCount = FirstRow To LastRow
If Range("B" & RowCount) = "SH" Then
Group = Range("E" & RowCount)
End If
Range("A" & RowCount) = Group
Next RowCount
End Sub


"Kirk P." wrote:

> Looking for some VBA help to automate this task. Basically I'm trying to
> create a columnar list of data that includes the account number in column A
> from a spreadsheet that displays the data in account number "groups"
>
> 1. For each instance of the text “SH” in column B, write the contents of
> the cell in that row from column E into the same row in column A
> 2. Copy the value just written into column A down to the row directly above
> the next instance of “SH” in column B
> 3. Repeat through entire spreadsheet.
>

Joel,

Thanks for the reply - it's almost perfect. In my data, the first instance
of "SH" actually occurs on the first row. Your code misses that first
instance, but does pick up everything after that perfectly.

Any ideas?

"Joel" wrote:

> Sub UpdateGroups()
>
> Set c = Columns("B").Find(what:="SH", _
> LookIn:=xlValues, lookat:=xlWhole)
> If c Is Nothing Then
> MsgBox ("Cannot find ""SH""! Exiting Macro")
> Exit Sub
> Else
> FirstRow = c.Row
> End If
>
> LastRow = Range("B" & Rows.Count).End(xlUp).Row
>
> For RowCount = FirstRow To LastRow
> If Range("B" & RowCount) = "SH" Then
> Group = Range("E" & RowCount)
> End If
> Range("A" & RowCount) = Group
> Next RowCount
> End Sub
>
>
> "Kirk P." wrote:
>
> > Looking for some VBA help to automate this task. Basically I'm trying to
> > create a columnar list of data that includes the account number in column A
> > from a spreadsheet that displays the data in account number "groups"
> >
> > 1. For each instance of the text “SH” in column B, write the contents of
> > the cell in that row from column E into the same row in column A
> > 2. Copy the value just written into column A down to the row directly above
> > the next instance of “SH” in column B
> > 3. Repeat through entire spreadsheet.
> >

The only way that code willnot work is as follows

1) there is no data in column e on the 1st row.
2) The is a blank character in cell B1. the code will only work is cell B1
contains SH and nothing else
3) SH is not capitalized in cell B1. one of the letters is in lower case.

"Kirk P." wrote:

> Joel,
>
> Thanks for the reply - it's almost perfect. In my data, the first instance
> of "SH" actually occurs on the first row. Your code misses that first
> instance, but does pick up everything after that perfectly.
>
> Any ideas?
>
> "Joel" wrote:
>
> > Sub UpdateGroups()
> >
> > Set c = Columns("B").Find(what:="SH", _
> > LookIn:=xlValues, lookat:=xlWhole)
> > If c Is Nothing Then
> > MsgBox ("Cannot find ""SH""! Exiting Macro")
> > Exit Sub
> > Else
> > FirstRow = c.Row
> > End If
> >
> > LastRow = Range("B" & Rows.Count).End(xlUp).Row
> >
> > For RowCount = FirstRow To LastRow
> > If Range("B" & RowCount) = "SH" Then
> > Group = Range("E" & RowCount)
> > End If
> > Range("A" & RowCount) = Group
> > Next RowCount
> > End Sub
> >
> >
> > "Kirk P." wrote:
> >
> > > Looking for some VBA help to automate this task. Basically I'm trying to
> > > create a columnar list of data that includes the account number in column A
> > > from a spreadsheet that displays the data in account number "groups"
> > >
> > > 1. For each instance of the text “SH” in column B, write the contents of
> > > the cell in that row from column E into the same row in column A
> > > 2. Copy the value just written into column A down to the row directly above
> > > the next instance of “SH” in column B
> > > 3. Repeat through entire spreadsheet.
> > >

I see the problem - the Find does not start in the first row, even if the
Find criteria are met. I confirmed this by doing a Find on column B for
"SH". It doesn't find anything until row 850 even through SH does exist in
row 1.

So the question is, is there any way to override this behavior (skipping row
1?)

"Joel" wrote:

> The only way that code willnot work is as follows
>
> 1) there is no data in column e on the 1st row.
> 2) The is a blank character in cell B1. the code will only work is cell B1
> contains SH and nothing else
> 3) SH is not capitalized in cell B1. one of the letters is in lower case.
>
> "Kirk P." wrote:
>
> > Joel,
> >
> > Thanks for the reply - it's almost perfect. In my data, the first instance
> > of "SH" actually occurs on the first row. Your code misses that first
> > instance, but does pick up everything after that perfectly.
> >
> > Any ideas?
> >
> > "Joel" wrote:
> >
> > > Sub UpdateGroups()
> > >
> > > Set c = Columns("B").Find(what:="SH", _
> > > LookIn:=xlValues, lookat:=xlWhole)
> > > If c Is Nothing Then
> > > MsgBox ("Cannot find ""SH""! Exiting Macro")
> > > Exit Sub
> > > Else
> > > FirstRow = c.Row
> > > End If
> > >
> > > LastRow = Range("B" & Rows.Count).End(xlUp).Row
> > >
> > > For RowCount = FirstRow To LastRow
> > > If Range("B" & RowCount) = "SH" Then
> > > Group = Range("E" & RowCount)
> > > End If
> > > Range("A" & RowCount) = Group
> > > Next RowCount
> > > End Sub
> > >
> > >
> > > "Kirk P." wrote:
> > >
> > > > Looking for some VBA help to automate this task. Basically I'm trying to
> > > > create a columnar list of data that includes the account number in column A
> > > > from a spreadsheet that displays the data in account number "groups"
> > > >
> > > > 1. For each instance of the text “SH” in column B, write the contents of
> > > > the cell in that row from column E into the same row in column A
> > > > 2. Copy the value just written into column A down to the row directly above
> > > > the next instance of “SH” in column B
> > > > 3. Repeat through entire spreadsheet.
> > > >

You can either eliminate the SET C statement and make firstRow = 1 or change
the SET C as follows

Set c = Columns("B").Find(what:="SH", _
LookIn:=xlValues, lookat:=xlWhole, after:=Range("B" & Rows.Count))


"Kirk P." wrote:

> I see the problem - the Find does not start in the first row, even if the
> Find criteria are met. I confirmed this by doing a Find on column B for
> "SH". It doesn't find anything until row 850 even through SH does exist in
> row 1.
>
> So the question is, is there any way to override this behavior (skipping row
> 1?)
>
> "Joel" wrote:
>
> > The only way that code willnot work is as follows
> >
> > 1) there is no data in column e on the 1st row.
> > 2) The is a blank character in cell B1. the code will only work is cell B1
> > contains SH and nothing else
> > 3) SH is not capitalized in cell B1. one of the letters is in lower case.
> >
> > "Kirk P." wrote:
> >
> > > Joel,
> > >
> > > Thanks for the reply - it's almost perfect. In my data, the first instance
> > > of "SH" actually occurs on the first row. Your code misses that first
> > > instance, but does pick up everything after that perfectly.
> > >
> > > Any ideas?
> > >
> > > "Joel" wrote:
> > >
> > > > Sub UpdateGroups()
> > > >
> > > > Set c = Columns("B").Find(what:="SH", _
> > > > LookIn:=xlValues, lookat:=xlWhole)
> > > > If c Is Nothing Then
> > > > MsgBox ("Cannot find ""SH""! Exiting Macro")
> > > > Exit Sub
> > > > Else
> > > > FirstRow = c.Row
> > > > End If
> > > >
> > > > LastRow = Range("B" & Rows.Count).End(xlUp).Row
> > > >
> > > > For RowCount = FirstRow To LastRow
> > > > If Range("B" & RowCount) = "SH" Then
> > > > Group = Range("E" & RowCount)
> > > > End If
> > > > Range("A" & RowCount) = Group
> > > > Next RowCount
> > > > End Sub
> > > >
> > > >
> > > > "Kirk P." wrote:
> > > >
> > > > > Looking for some VBA help to automate this task. Basically I'm trying to
> > > > > create a columnar list of data that includes the account number in column A
> > > > > from a spreadsheet that displays the data in account number "groups"
> > > > >
> > > > > 1. For each instance of the text “SH” in column B, write the contents of
> > > > > the cell in that row from column E into the same row in column A
> > > > > 2. Copy the value just written into column A down to the row directly above
> > > > > the next instance of “SH” in column B
> > > > > 3. Repeat through entire spreadsheet.
> > > > >

Thanks Joel!

"Joel" wrote:

> You can either eliminate the SET C statement and make firstRow = 1 or change
> the SET C as follows
>
> Set c = Columns("B").Find(what:="SH", _
> LookIn:=xlValues, lookat:=xlWhole, after:=Range("B" & Rows.Count))
>
>
> "Kirk P." wrote:
>
> > I see the problem - the Find does not start in the first row, even if the
> > Find criteria are met. I confirmed this by doing a Find on column B for
> > "SH". It doesn't find anything until row 850 even through SH does exist in
> > row 1.
> >
> > So the question is, is there any way to override this behavior (skipping row
> > 1?)
> >
> > "Joel" wrote:
> >
> > > The only way that code willnot work is as follows
> > >
> > > 1) there is no data in column e on the 1st row.
> > > 2) The is a blank character in cell B1. the code will only work is cell B1
> > > contains SH and nothing else
> > > 3) SH is not capitalized in cell B1. one of the letters is in lower case.
> > >
> > > "Kirk P." wrote:
> > >
> > > > Joel,
> > > >
> > > > Thanks for the reply - it's almost perfect. In my data, the first instance
> > > > of "SH" actually occurs on the first row. Your code misses that first
> > > > instance, but does pick up everything after that perfectly.
> > > >
> > > > Any ideas?
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > Sub UpdateGroups()
> > > > >
> > > > > Set c = Columns("B").Find(what:="SH", _
> > > > > LookIn:=xlValues, lookat:=xlWhole)
> > > > > If c Is Nothing Then
> > > > > MsgBox ("Cannot find ""SH""! Exiting Macro")
> > > > > Exit Sub
> > > > > Else
> > > > > FirstRow = c.Row
> > > > > End If
> > > > >
> > > > > LastRow = Range("B" & Rows.Count).End(xlUp).Row
> > > > >
> > > > > For RowCount = FirstRow To LastRow
> > > > > If Range("B" & RowCount) = "SH" Then
> > > > > Group = Range("E" & RowCount)
> > > > > End If
> > > > > Range("A" & RowCount) = Group
> > > > > Next RowCount
> > > > > End Sub
> > > > >
> > > > >
> > > > > "Kirk P." wrote:
> > > > >
> > > > > > Looking for some VBA help to automate this task. Basically I'm trying to
> > > > > > create a columnar list of data that includes the account number in column A
> > > > > > from a spreadsheet that displays the data in account number "groups"
> > > > > >
> > > > > > 1. For each instance of the text “SH” in column B, write the contents of
> > > > > > the cell in that row from column E into the same row in column A
> > > > > > 2. Copy the value just written into column A down to the row directly above
> > > > > > the next instance of “SH” in column B
> > > > > > 3. Repeat through entire spreadsheet.
> > > > > >