Data Manipulation

[Kirk P.]

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.

 

[Mike]

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 address removed). I will take a look at and see what I can do.

 

[Joel]

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.]

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]

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.]

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]

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.]

Thanks Joel!

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