Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Col C based on data in Col B
I have to format a report every day that is imported from SQL to Excel. My
problem is that I am stuck on trying to "insert" text descriptions in Column C based on what is in Column B. The number of rows may vary from day to day (ie: one day the report is 315 rows and the next it may be 278 or 480). So, the total range of Col B would extend from (B2:end) on any given day. In plain language, If any of the data in Range (B:B) begins with "ML*" insert UPPERCASE "ABC" in Col C2 or If any of the data in Range(B:B) begins with "W*" insert UPPERCASE "ABC" in Col C2... (***the data inserted goes in the cell directly to the right of the data in cell Bx) If any data in B2 contains "*105*" or contains "*SR*" or contains "*KBV*" or contains "*KR*" insert UPPERCASE "DEF" in C2, etc... Any data left in C2:end not already formatted with any of the above stuff, "ABC" I have written a macro to do the initial formatting that gets rid of subtotals, deletes a column, etc...but cannot go any further with my limited programming skills, so any code that can do what I want as specified above will be inserted into the macro I already have. Any assistance would be appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200912/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Col C based on data in Col B
Your instructions aren't all that clear. In places it could be interpreted multiple ways.
In any case, I've done my best, and probably you'll be able to nut something out based on the code supplied. Sub test() Const cFirstRow = 2 Const cCheckCol = "B", cDestCol = "C" Dim i As Long, lngLastRow As Long, bln As Boolean With ActiveSheet lngLastRow = .Cells(Rows.Count, cCheckCol).End(xlUp).Row 'up front check for ML* W* bln = False For i = cFirstRow To lngLastRow If Left(.Cells(i, cCheckCol), 3) = "ML*" Or _ Left(.Cells(i, cCheckCol), 2) = "W*" Then bln = True Exit For End If Next For i = cFirstRow To lngLastRow If bln Then .Cells(i, cDestCol) = .Cells(i, cDestCol) & "ABC" If InStr(1, .Cells(i, cCheckCol), "*105*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*SR*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*KBV*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*KR*") 0 Then .Cells(i, cDestCol) = .Cells(i, cDestCol) & "DEF" Else .Cells(i, cDestCol) = .Cells(i, cDestCol) & "ABC" End If Next End With End Sub Cheers, Rob On 10-Dec-2009 14:09, DPCpresto via OfficeKB.com wrote: I have to format a report every day that is imported from SQL to Excel. My problem is that I am stuck on trying to "insert" text descriptions in Column C based on what is in Column B. The number of rows may vary from day to day (ie: one day the report is 315 rows and the next it may be 278 or 480). So, the total range of Col B would extend from (B2:end) on any given day. In plain language, If any of the data in Range (B:B) begins with "ML*" insert UPPERCASE "ABC" in Col C2 or If any of the data in Range(B:B) begins with "W*" insert UPPERCASE "ABC" in Col C2... (***the data inserted goes in the cell directly to the right of the data in cell Bx) If any data in B2 contains "*105*" or contains "*SR*" or contains "*KBV*" or contains "*KR*" insert UPPERCASE "DEF" in C2, etc... Any data left in C2:end not already formatted with any of the above stuff, "ABC" I have written a macro to do the initial formatting that gets rid of subtotals, deletes a column, etc...but cannot go any further with my limited programming skills, so any code that can do what I want as specified above will be inserted into the macro I already have. Any assistance would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Col C based on data in Col B
Aloha Rob, I'll give your code a try and see how it works...sorry for the
confusion of my description... Dave Rob van Gelder wrote: Your instructions aren't all that clear. In places it could be interpreted multiple ways. In any case, I've done my best, and probably you'll be able to nut something out based on the code supplied. Sub test() Const cFirstRow = 2 Const cCheckCol = "B", cDestCol = "C" Dim i As Long, lngLastRow As Long, bln As Boolean With ActiveSheet lngLastRow = .Cells(Rows.Count, cCheckCol).End(xlUp).Row 'up front check for ML* W* bln = False For i = cFirstRow To lngLastRow If Left(.Cells(i, cCheckCol), 3) = "ML*" Or _ Left(.Cells(i, cCheckCol), 2) = "W*" Then bln = True Exit For End If Next For i = cFirstRow To lngLastRow If bln Then .Cells(i, cDestCol) = .Cells(i, cDestCol) & "ABC" If InStr(1, .Cells(i, cCheckCol), "*105*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*SR*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*KBV*") 0 Or _ InStr(1, .Cells(i, cCheckCol), "*KR*") 0 Then .Cells(i, cDestCol) = .Cells(i, cDestCol) & "DEF" Else .Cells(i, cDestCol) = .Cells(i, cDestCol) & "ABC" End If Next End With End Sub Cheers, Rob I have to format a report every day that is imported from SQL to Excel. My problem is that I am stuck on trying to "insert" text descriptions in Column [quoted text clipped - 20 lines] will be inserted into the macro I already have. Any assistance would be appreciated. -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200912/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Col C based on data in Col B
Rob...I tried this code and it works, sort of. It actually places "ABC" in
every cell in Column C regardless of what is in Col B. It works except for that... Dave DPCpresto wrote: Aloha Rob, I'll give your code a try and see how it works...sorry for the confusion of my description... Dave Your instructions aren't all that clear. In places it could be interpreted multiple ways. In any case, I've done my best, and probably you'll be able to nut something out based on the code supplied. [quoted text clipped - 42 lines] will be inserted into the macro I already have. Any assistance would be appreciated. -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200912/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting a cell based on other cells' data | Excel Worksheet Functions | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Conditional Formatting based on data in adjacent cell & restrict save without required data | Excel Programming | |||
Formatting Rows of Data based on Column Data | Excel Worksheet Functions | |||
How do I use conditional formatting on all rows based on data | Excel Discussion (Misc queries) |