VBA change cell values based on the text in one column and a number in another column

[zack8576]

Hi, I am very new to VBA. I need to modify some of the values in column D based on the text in K and the number in L.
We will use column K to determine the type of item, and use the number in L to determine which range this number falls in. It then pulls the correct item# from the master.xlsx and replaces the value in column D.
If the number in L falls in 12"-19", we will assign an item# for 12"; if it falls in 20-32, we will assign it item# for 24"; if it falls in 33"-42, we will assign the item# for 36"...etc
the excel file in the first screenshot is located on a company server, the master.xlsx is on another drive on company server (we can save this file locally if needed)
here is an example: In rows 2, 3, and 7 in screenshot 1, there are:

C BOX (6" WALL) with a height of 14", which is in the range of 12-19, we will need to assign cell D2 the item# F22122J from cell B36 in the master.xlsx file (screenshot 2) D BOX (6" WALL) with a height of 26", which is in the range of 20-32, we will need to assign cell D3 the item# F22133J from cell B101 in the master.xlsx file (screenshot 3) C BOX (6" WALL) with a height of 20", which is in the range of 20-32, we will need to assign cell D7 the item# F22123J from cell B37 in the master.xlsx file (screenshot 4)

Any help is greatly appreciated !

 

[zack8576]

Hi Zack, I'm afraid this is a bit beyond me. As far as adding the height suffixes to the end of the descriptions - not a problem. The following code is an upgrade of what I provided earlier:

For Each c In rng
        If c Like "*BOX*" Or _
            c Like "*BASE*" Or _
            c Like "*RISER*" Or _
            c Like "*COLLAR*" Then
                c = c & " " & c.Offset(, 1)
        End If
    Next

When it comes to determining which 'range' the different items fall into - again, not a problem:

x = Application.WorksheetFunction.Floor(Val(c.Offset(, 1)), 12)

where 'x' is the rounded down number to the nearest factor of 12 (seems to be how they're classified)

The problems start when it comes to comparing the text parts of the descriptions in the csv with the corresponding strings in the master file. They are significantly different in their format/arrangement, and I can't figure out a way to reconcile this. Sorry Zack, I did try

For Each c In rng
        If c Like "*BOX*" Or _
            c Like "*BASE*" Or _
            c Like "*RISER*" Or _
            c Like "*COLLAR*" Then
                c = c & " " & c.Offset(, 1)
        End If
    Next

This is very elegant, thanks Kevin.

 

[bebo021999]

So, is it solved?

 

[zack8576]

So, is it solved?

Not yet, this is the revised code for another problem. I believe Kevin is very close to solving the issue though

 

[kevin9999]

Not yet, this is the revised code for another problem. I believe Kevin is very close to solving the issue though

Few hours away ?

 

[bebo021999]

Try below code.

Option Explicit
Sub test()
Dim i&
Dim wsM As Worksheet, wsC As Worksheet
Dim cell As Range, cellM As Range
Dim st As String
Set wsM = Workbooks("Master.xlsx").Sheets("Sheet1") ' This is file Master. Adjust to actual Path
Set wsC = Workbooks("CSV.xlsm").Sheets("Sheet1") ' This is Destination file. Adjust to actual Path
With wsC
    For Each cell In .Range("K2:K" & .Cells(Rows.Count, "K").End(xlUp).Row)
        st = ""
        st = UCase(Trim(Left(cell, InStr(cell, "(") - 1))) ' Assum all items in column K contain "("
        For i = wsM.Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1 ' searching from bottom, assums inches# is sorted
            Set cellM = wsM.Cells(i, "A")
            If cellM <> "" Then
                If UCase(cellM) Like st & "*" Then
                    If Val(Mid(cellM, Len(cellM) - 2, 2)) <= Val(Left(cell.Offset(, 1), 2)) Then
                        cell.Offset(, -7).Value = cellM.Offset(, 1).Value
                        Exit For
                    End If
                Else
                    If UCase(cellM) Like "*" & "DIA" & "*" & Right(st, 3) & "*" Then
                        If Val(Mid(cellM, Len(cellM) - 2, 2)) <= Val(Left(cell.Offset(, 1), 2)) Then
                            cell.Offset(, -7).Value = cellM.Offset(, 1).Value
                            Exit For
                        End If
                    End If
                End If
            End If
        Next
    Next
End With
End Sub

 

[kevin9999]

Zack - could you try @bebo021999 's code and see how it goes? If it gives you what you want, it'll save me a lot of time

 

[zack8576]

Zack - could you try @bebo021999 's code and see how it goes? If it gives you what you want, it'll save me a lot of time

Kevin, I will try it and see how it goes. I will let you know

 

[zack8576]

Try below code.

Option Explicit
Sub test()
Dim i&
Dim wsM As Worksheet, wsC As Worksheet
Dim cell As Range, cellM As Range
Dim st As String
Set wsM = Workbooks("Master.xlsx").Sheets("Sheet1") ' This is file Master. Adjust to actual Path
Set wsC = Workbooks("CSV.xlsm").Sheets("Sheet1") ' This is Destination file. Adjust to actual Path
With wsC
    For Each cell In .Range("K2:K" & .Cells(Rows.Count, "K").End(xlUp).Row)
        st = ""
        st = UCase(Trim(Left(cell, InStr(cell, "(") - 1))) ' Assum all items in column K contain "("
        For i = wsM.Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1 ' searching from bottom, assums inches# is sorted
            Set cellM = wsM.Cells(i, "A")
            If cellM <> "" Then
                If UCase(cellM) Like st & "*" Then
                    If Val(Mid(cellM, Len(cellM) - 2, 2)) <= Val(Left(cell.Offset(, 1), 2)) Then
                        cell.Offset(, -7).Value = cellM.Offset(, 1).Value
                        Exit For
                    End If
                Else
                    If UCase(cellM) Like "*" & "DIA" & "*" & Right(st, 3) & "*" Then
                        If Val(Mid(cellM, Len(cellM) - 2, 2)) <= Val(Left(cell.Offset(, 1), 2)) Then
                            cell.Offset(, -7).Value = cellM.Offset(, 1).Value
                            Exit For
                        End If
                    End If
                End If
            End If
        Next
    Next
End With
End Sub

View attachment 54549

Bebo, thank you! I will test it, and let you know how it goes

 

[bebo021999]

Good luck and TIA your feedback!

 

[zack8576]

Good luck and TIA your feedback!

Bebo,I appreciate your help, one question, the text format in the csv file will actually be the same as the format in the master file. Like C Box,6"W,12", C Box,6"W,24"...etc
So there won't be parenthesis, how do I modify this line of code then? seems like so far this is the only thing causing me issues..

st = UCase(Trim(Left(cell, InStr(cell, "(") - 1)))