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/