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

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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 !

1641347396582-png.54437


1641347865090.png

1641348094513.png

1641348214953.png
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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 :confused:
VBA Code:
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
452

ADVERTISEMENT

Try below code.
VBA 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

Capture.JPG
 

kevin9999

Board Regular
Joined
Aug 28, 2020
Messages
129
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Try below code.
VBA 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
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,573
Messages
5,765,182
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top