Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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 . 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Manipulation
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data manipulation help | Excel Worksheet Functions | |||
Data Manipulation. | Excel Discussion (Misc queries) | |||
Need help with some data manipulation | Excel Worksheet Functions | |||
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation | Excel Programming | |||
Data Manipulation | Excel Programming |