• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 80
  • Last Modified:

Modification of VBA to use named range instead of offset

I had this question after viewing Modification of VBA to use scripting dictionary instead of Evaluate(Formula).

Rgonzo1971 was so kind helping me on the code below.  it currently uses offset function. it works if my data columns are static.

sometimes my data columns move around and for that i have dynamic row and dynamic columns named ranges.  is it possibe to modify the code so that instead of using offset. it uses the named ranges which is in the attached workbook.

Many thanks.

Sub macro1()
For Each c In Range(Sheets("Data").Range("a2"), Sheets("Data").Range("a" & Rows.Count).End(xlUp))
    If (c.Value = 2011 Or c.Value = 2012) And c.Offset(, 1).Value <> 111 And _
            (c.Offset(, 2).Value Like "[5-7]*") Then
        mySum = mySum + c.Offset(, 3).Value
    End If
Next
Sheets("Main").Range("B2") = mySum
End Sub

Open in new window

EE.xlsb
0
Flora
Asked:
Flora
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro3()
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set ShD = Sheets("Data")
For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
    If (ShD.Cells(C.Row, intYearCol).Value = 2011 Or ShD.Cells(C.Row, intYearCol).Value = 2012) _
            And ShD.Cells(C.Row, intMonthCol).Value <> 111 And _
            ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
        mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
    End If
Next
Sheets("Main").Range("B3") = mySum
End Sub

Open in new window

Regards
1
 
FloraAuthor Commented:
Thank you very much. Thanks a million
0
 
FloraAuthor Commented:
i posted a follow up question. it seems like i have to create 16 macro to make it work, but there has to be an easy way.

https://www.experts-exchange.com/questions/29069652/VBA-Modification-Do-i-have-to-have-too-many-seperate-Subs-for-each-of-these-cells.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now