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

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.

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

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