Range Macro Not Functioning

Question

.

The following macro (CollectProjectItems) functions as designed. Applying the same logic, with a change in the Range, in the macro (CollectContractorItems) does not function as desired.

I am presuming the error is something I've overlooked and of course ... for the life of me ... I cannot identify my error.

Need a fresh set of eyes.

Thank you ahead of time.

Sub UpdateCharts()
    CollectProjectItems
    CollectContractorItems
End Sub

Sub CollectProjectItems()
On Error Resume Next
    MyDate = Format(Date, "mmm") & "-" & Right(Year(Date), 2)
    For Each cl In Range("A3", Range("A" & Rows.Count).End(xlUp))
        wproj = Application.Match(cl.Value, Columns(10), 0)
        
        If IsNumeric(wproj) Then
            MyMonth = Application.Match(MyDate, Rows(wproj + 1), 0)
            Cells(wproj + 2, MyMonth) = cl.Offset(, 1)
            Cells(wproj + 3, MyMonth) = cl.Offset(, 2)
        End If
    Next
End Sub

Sub CollectContractorItems()
On Error Resume Next
    MyDate = Format(Date, "mmm") & "-" & Right(Year(Date), 2)
    For Each cl In Range("E3", Range("E" & Rows.Count).End(xlUp))
        wproj = Application.Match(cl.Value, Columns(25), 0)
        
        If IsNumeric(wproj) Then
            MyMonth = Application.Match(MyDate, Rows(wproj + 1), 0)
            Cells(wproj + 2, MyMonth) = cl.Offset(, 1)
            Cells(wproj + 3, MyMonth) = cl.Offset(, 2)
        End If
    Next
End Sub

The second macro does not complete the required edits in Col AG. It duplicates the same edit as the first macro for Col R.

I don't understand how to change the second macro so it affects edits in Cols Z:AK .

???

Download example workbook : Macro Error

Remove the On Error Resume Next if you need to find out what's going on. That is a bad "code smell" here - there's no reason for it to be there. — Tim Williams, Aug 14 '20 at 15:38
You also need to test `MyMonth` using IsError() to make sure you got a match. And qualify all of your range calls with an explicit worksheet object. — Tim Williams, Aug 14 '20 at 15:40
Ok on removing "On Error ..." I don't understand your second recommendation. — Jerry, Aug 14 '20 at 15:48
`Match()` returns an error if there's no match, so you should *always* test for that using IsError(), so you can decide what to do in that case. Range references like `Cells()` (in a regular code module) always default to the ActiveSheet, and code which depends on a certain sheet being active is prone to fail unexpectedly. Even if you really mean to operate on ActiveSheet, it's best to be explicit about that in your code. So `ActiveSheet.Cells()` and not just `Cells()` — Tim Williams, Aug 14 '20 at 15:54
https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Aug 14 '20 at 15:55
OK .. hopefully I've correctly understood. Here is the edited code ... trusting it is correct ? Sub CollectContractorItems() Dim var1 Dim isErr1 As Boolean var1 = MyMonth MsgBox IsError(var1) It returns FALSE ... meaning No Error ??? If that is accurate, why is the macro not performing as desired ? — Jerry, Aug 14 '20 at 16:06

Tim Williams · Answer 1 · 2020-08-14T19:22:33.237

1

Like this:

Sub CollectContractorItems()
    Const COL_CONTRACTORS As Long = 25
    Dim MyDate As String, cl As Range, ws As Worksheet, wproj, MyMonth
    Dim rngDates As Range, dtCol As Long

    Set ws = ActiveSheet 'or some specific sheet
    MyDate = Format(Date, "mmm") & "-" & Right(Year(Date), 2)

    For Each cl In ws.Range("E3:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row).Cells
        wproj = Application.Match(cl.Value, ws.Columns(COL_CONTRACTORS), 0)
        
        If Not IsError(wproj) Then
            'get the range with dates
            Set rngDates = ws.Cells(wproj, COL_CONTRACTORS).Offset(1, 1).Resize(1, 12)
            MyMonth = Application.Match(MyDate, rngDates, 0) 'search only in the specific range
            If Not IsError(MyMonth) Then
                dtCol = rngDates.Cells(MyMonth).Column 'get the column number
                ws.Cells(wproj + 2, dtCol) = cl.Offset(, 1)
                ws.Cells(wproj + 3, dtCol) = cl.Offset(, 2)
            End If
        End If
    Next
End Sub

edited Aug 14 '20 at 19:22

answered Aug 14 '20 at 16:15

Tim Williams

154,628
8
97
125

I understand and thank you. However, it still doesn't change the values in Cols Z:AK. It is changing the values for Cols K:V which is not the goal. – Jerry Aug 14 '20 at 16:17
In that case the value of MyMonth is not what you expect. There's no way for me to predict that... You need to step through the code and see where it's going wrong... – Tim Williams Aug 14 '20 at 16:22
Why does the identical code function as desired for Cols K:V .... but it does not for Cols Z:AK ? If I correctly understand the macro logic ... indicating ws.Columns(25) should tell Excel to look at the second group of columns. – Jerry Aug 14 '20 at 16:25
1

`Application.Match(MyDate, ws.Rows(wproj + 1), 0)` will find the first match in that row for (eg) "Jan-20". Since you have a set of month-year values in K:V on the same row, it's going to find that before it finds any values in Z:AK... Your "Project" and "subcontractor" calendars can't be on the same rows. – Tim Williams Aug 14 '20 at 16:38
Hmmm ... in that case, it appears I need to research another method to obtain the correct column in the second set of months. – Jerry Aug 14 '20 at 19:14
See my updates above - you can search only the specific range of cells instead of the whole row. – Tim Williams Aug 14 '20 at 19:23
BINGO !!! ..... Thank you for sticking with me and your assistance. I understood you were in "teaching mode" and I appreciate that. ... I just wish my VBA knowledge were more extensive. What you've provided would have taken me weeks or longer to obtain. Again, thank you for the instruction. Cheers ! – Jerry Aug 14 '20 at 19:40

Range Macro Not Functioning

1 Answers1