Formatting Col C based on data in Col B

  • Thread starter DPCpresto via OfficeKB.com
  • Start date
D

DPCpresto via OfficeKB.com

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

Rob van Gelder

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
 
D

DPCpresto via OfficeKB.com

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.

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

DPCpresto via OfficeKB.com

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top