Identify a given range with VBA

Question

I have a list of part numbers as shown below, each one has a range start and end value which needs to replace the *** in the part number.

I am trying to identify the range start and end value, look them up on the range list on sheet2 (RangeTable) and then concatenate that with the part number individually, so for each part number in column A, a range value defined in column B and C will be concatenated on sheet3 (PartNumbers).

The code is not identifying the Range Start and End value and is creating a part number for all values on the Range Table.

Sub CompilePartNums()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim LR3 As Long
Dim Ctr1 As Long
Dim Ctr2 As Long

Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("RangeTable")
Set WS3 = Worksheets("PartNumbers")

With WS1
    LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With WS2
    LR2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

LR3 = 2
For Ctr1 = 2 To LR1
    For Ctr2 = 2 To LR2
        Select Case WS2.Cells(Ctr2, 1)
            Case WS1.Cells(Ctr1, 2) To WS1.Cells(Ctr1, 3)
                WS3.Cells(LR3, 1) = replace(WS1.Cells(Ctr1, 1), "***", WS2.Cells(Ctr2, 1))
                LR3 = LR3 + 1
        End Select

    Next
Next

End Sub

Sheet1 (Tabelle1) Looks like this and continues down to ROW1210

Sheet2 (RangeTable) looks like this, the values in this need to stay in this order which are not sequential

Sheet3 (PartNumbers) is the results that are output, it should stop at 390 however it continues thru all the RangeTable values.

Do I understand this correctly? Do you have a list of part numbers in column A of the Tabelle1 sheet, each one in a format something like xxxx***xxx (or maybe just xxxx*** - it probably doesn't matter), and another list of part numbers (in the same format) in column A of the RangeTable sheet. In columns B & C of that sheet you have some values (let's say 10 in B and 22 in C). You want to generate records in column A of the PartNumbers sheet, one row saying xxxx10xxx, the next saying xxxx11xxx, etc, and then you want to do the same with the next part number on the Tabelle1 sheet? — YowE3K
I guess it would help people if you posted a screenshot of your problem and the desired solution. — Mister 832
you'd better add more info about your problem in connection with posted screenshots — user3598756
Your question says a range value defined in column B and C, but none of the screenshots have anything in column B and C. — YowE3K

YowE3K YowE3K · Accepted Answer · 2016-08-19T19:11:52

I'll post this as an "answer" for the moment, simply because it is too complicated to explain in a comment, but I think it might have something to do with your issue.

You have a Select Case statement of:

Select Case WS2.Cells(Ctr2, 1)
    Case WS1.Cells(Ctr1, 2) To WS1.Cells(Ctr1, 3)
        WS3.Cells(LR3, 1) = replace(WS1.Cells(Ctr1, 1), "***", WS2.Cells(Ctr2, 1))
        LR3 = LR3 + 1
End Select

That statement is effectively equivalent to

If WS2.Cells(Ctr2, 1) >= WS1.Cells(Ctr1, 2) And _
   WS2.Cells(Ctr2, 1) <= WS1.Cells(Ctr1, 3) Then
    WS3.Cells(LR3, 1) = replace(WS1.Cells(Ctr1, 1), "***", WS2.Cells(Ctr2, 1))
    LR3 = LR3 + 1
End If

And that statement is basically saying that if a record in column A of the RangeTable sheet is greater than or equal to the value in column B of the Tabelle1 sheet and less than or equal to the value in column C of the Tabelle1 sheet, then create a new cell in column A of the PartNumbers sheet with a value of column A of the Tabelle1 sheet (with any "***" in that value replaced by the value in column A of the RangeTable sheet).

Considering that none of your sheets have anything in column B or C, I can't understand how your code managed to write anything to the PartNumbers sheet ?!?!

Identify a given range with VBA

1 Answers