editing VBA code

[BigBill7]

Hello, I am currently working with a macro that was created by a previous employee and have been trying to make sense of their code as there are no notes left for us to work with. I am currently trying to add in the highlighted columns into the corresponding colors and increase the column range that the macro runs from H to J. Currently it only runs to column H and I want to add in these color coded columns and increase the range to column J as well. I have ran the macro a couple of times and figured out the code will not use the data in columns I and J when inserted.

[SamT]

I believe that this is a true version of your code, (It might have a transposition since I mostly copied and pasted.) All I tried to do was clean out the artifacts left by the Macro Recorder. I think it will be more self explanatory than the original.


Option Explicit

Sub ACM()
Dim CloseRow As Long, LastRow As Long, AccRow As Long
Dim Closings As Worksheet, Accruals As Worksheet
Dim Titell, AccNum, LSNL, Principle, Income, CUSIP 'Represent Headers on Closing Accounts Sheet

Set Closings = Closings
Set Accruals = Accruals

LastRow = Closings.Cells(Rows.Count, 4).End(xlUp).Row + 1
CloseRow = 1
AccRow = 2

With Accruals
Titell = Right(.Cells(1, "C").Value, 9) 'Titell(sic) To avoid Keywords
AccNum = Right(.Cells(1, "D").Value, 9)
LSNL = Right(.Cells(1, "E").Value, 9)
Principle = Right(.Cells(1, "F").Value, 9)
Income = Right(.Cells(1, "G").Value, 9)
CUSIP = Right(.Cells(1, "H").Value, 9)

'Transpose Headers to Column K 'Not really used anywhehere in this version
.Cells(2, "K").Value = Titell
.Cells(3, "K").Value = AccNum
.Cells(4, "K").Value = LSNL
.Cells(5, "K").Value = Principle
.Cells(6, "K").Value = Income
.Cells(7, "K").Value = CUSIP

Do Until CloseRow = LastRow
If Closings.Cells(CloseRow, "E").Value = "A1" And (Closings.Cells(CloseRow, "I").Value > 0 And Closings.Cells(CloseRow, "I").Value < 25) Then
.Cells(AccRow, "A").Value = Closings.Cells(CloseRow, "D").Value
.Cells(AccRow, "I").Value = Closings.Cells(CloseRow, "H").Value

Select Case .Cells(AccRow, "I").Value
Case Is = Titell: .Cells(AccRow, "C").Value = Closings.Cells(CloseRow, "I").Value
Case Is = AccNum: .Cells(AccRow, "D").Value = Closings.Cells(CloseRow, "I").Value
Case Is = LSNL: .Cells(AccRow, "E").Value = Closings.Cells(CloseRow, "I").Value
Case Is = Principle: .Cells(AccRow, "F").Value = Closings.Cells(CloseRow, "I").Value
Case Is = Income: .Cells(AccRow, "G").Value = Closings.Cells(CloseRow, "I").Value
Case Is = CUSIP: .Cells(AccRow, "H").Value = Closings.Cells(CloseRow, "I").Value
End Select
End If
AccRow = AccRow + 1
CloseRow = CloseRow + 1
Loop
.Range("I:K").Delete
End With
End Sub

[p45cal]

Let's see if I've got this right:
For each row in the ACM Closing Accounts sheet, if the LSNL column is A1 the Total column is >0 and < 25 you want to:

copy the Admin: Account Number: (column D) of that sheet to the same headed column (column A) of the ACM Closing Account Accruals sheet
put the Total value in the same column of the ACM Closing Account Accruals sheet which has the same last 9 digits of the ACM CUSIP column (column H).



Will there ever be more than one column in the ACM Closing Account Accruals with the same last 9 digits?
If the answer to that is No then if you prime your ACM Closing Account Accruals sheet, as you are doing now, with its row 1 data, but add in any column you choose in row 1 the exact text ACM CUSIP, the cusip will be added to that column.
Run this macro instead of yours:
Sub blah()
Dim r As Long, LastRow As Long, r2 As Long
Set DestnSht = Sheets("ACM Closing Account Accruals")
r2 = 2
With DestnSht
Set rngDestnHeaders = Range(.Cells(1), .Cells(1, .Columns.Count).End(xlToLeft))
ReDim ColumnMatch(1 To rngDestnHeaders.Columns.Count) As String
For i = 2 To UBound(ColumnMatch)
If Len(rngDestnHeaders.Cells(i).Value) > 0 Then ColumnMatch(i) = Right(rngDestnHeaders.Cells(i).Value, 9)
Next i
CusipColm = Application.Match("ACM CUSIP", ColumnMatch, 0)
End With
With Sheets("ACM Closing Accounts")
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
r2 = 2
For r = 2 To LastRow
If .Cells(r, 9).Value < 25 And .Cells(r, 5).Value = "A1" And .Cells(r, 9).Value > 0 Then 'Total >0, <25 and LSNL = "A1"
DestnSht.Cells(r2, "A").Value = .Cells(r, "D").Value 'copy admin accnt no.
DestnSht.Cells(r2, CusipColm).Value = .Cells(r, "H").Value 'copy CUSIP
colm = Application.Match(CStr(.Cells(r, 8).Value), ColumnMatch, 0)
If Not IsError(colm) Then
DestnSht.Cells(r2, colm).Value = .Cells(r, "I").Value 'copy total
End If
r2 = r2 + 1
End If
Next r
End With
End Sub
and see if it gives you what you want.

[BigBill7]

I got a "Type mismatch error" when running the new Macro.

[p45cal]

I got a "Type mismatch error" when running the new Macro.

Who are you talking to?

[BigBill7]

hey SamT I'm getting an "object variable or With block variable not set" when I run your code. Any idea why that might be?

[BigBill7]

Sorry talking to you p45cal

[p45cal]

I could get that error if I got this bit wrong:

but add in any column you choose in row 1 the exact text ACM CUSIP, the cusip will be added to that column.

[p45cal]

Click the button at cell K1 of the ACM Closing Accounts sheet of the attached.

[SamT]

hey SamT I'm getting an "object variable or With block variable not set" when I run your code. Any idea why that might be?
Well it's just your code Refactored. I don't really know what you want, so I just Refactored it to make it easier for you to see what it was already doing. I explained all that in that post. I also explained that I'm not 100% sure I Refactored it correctly.

If I am to troubleshoot what I wrote, I need to know exactly what code you pasted into your workbooks, what other code is still in there and exactly which line the error is Raised on. I can't see your monitor from my desk.



add in the highlighted columns into the corresponding colors and increase the column range that the macro runs from H to J.Say What? Details. details, details. We can't read minds. Also; Your code already references Column K, so that doesn't make sense at all.

[BigBill7]

Thank you for the help guys. The Macro works fine now and we are able to complete the task. I'll mark the thread as solved/