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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
failed to upload the first screenshot, here it is. Thanks!
1641349147840.png
 

kevin9999

Board Regular
Joined
Aug 28, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows
There's probably a stack of different ways you could approach this. One way that may be relatively easy to maintain would be to use a Select Case option. The following code should be run when the sheet containing the Part heights in column L is the Active sheet. I hope the code is fairly self-explanatory/easy to follow.

VBA Code:
Option Explicit
Sub ZackCase()
    Dim ws As Worksheet, c As Range, rng As Range, x As Long
    Set ws = ActiveSheet
    Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
   
    For Each c In rng
        x = Val(c)
        Select Case x
            Case 12 To 19
                c.Offset(, -8).Value = "F22122J"
            Case 20 To 32
                c.Offset(, -8).Value = "F22123J"
            Case 33 To 42
                c.Offset(, -8).Value = "F22124J"
        End Select
    Next
End Sub
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
There's probably a stack of different ways you could approach this. One way that may be relatively easy to maintain would be to use a Select Case option. The following code should be run when the sheet containing the Part heights in column L is the Active sheet. I hope the code is fairly self-explanatory/easy to follow.

VBA Code:
Option Explicit
Sub ZackCase()
    Dim ws As Worksheet, c As Range, rng As Range, x As Long
    Set ws = ActiveSheet
    Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
  
    For Each c In rng
        x = Val(c)
        Select Case x
            Case 12 To 19
                c.Offset(, -8).Value = "F22122J"
            Case 20 To 32
                c.Offset(, -8).Value = "F22123J"
            Case 33 To 42
                c.Offset(, -8).Value = "F22124J"
        End Select
    Next
End Sub
Kevin, thank you for the response. My original thought was maintain the master.xlsx as a library for these item# ( there are around 1000 of these item# BTW). And the value in D would always be modified by using the value in appropriate cell from the master file.
You are saying instead of doing that, it is a better idea to just write these item# into the lines of code right?

Also, this needs to run with the other few lines of code simultaneously or run right after the other lines are done, can I put both in the same sub ?

Option Explicit
Sub Zack()
Dim ws As Worksheet, lr As Long, c As Range
Set ws = ActiveSheet
lr = ws.Cells(Rows.Count, 11).End(xlUp).Row

For Each c In ws.Range("K2:K" & lr)
If c = "C BOX (6""" & " WALL)" Or _
c = "D BOX (6""" & " WALL)" Or _
c = "SAN MH(5""" & " WALL)" Or _
c = "MH 72""" & " DIA(8""" & " WALL)" Then
c = c & " " & c.Offset(, 1)
End If
Next c
End Sub


I will play with these lines this morning, I will let you know how this goes :)
 

kevin9999

Board Regular
Joined
Aug 28, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The elect Case option is really only useful for a relatively small number of choices. If there are, as you say, around 1000 item numbers, then a different approach would work better. It would be helpful to know all the workbook, worksheet & range names or references involved.
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
The elect Case option is really only useful for a relatively small number of choices. If there are, as you say, around 1000 item numbers, then a different approach would work better. It would be helpful to know all the workbook, worksheet & range names or references involved.
Kevin, thank you, here we go:

Workbooks:
at any given time, there are just 2 workbooks at the most. the workbook that is being modified is labeled as 2021120081.csv, or 2021120089.csv..etc the name of the file is always different, but the format is always consistent. This file is normally located on a company drive, lets call it O drive. this file can be moved to a local drive if needed.
The second workbook is the master.xlsx file, which contains all the item# we use to reference in the file above, this is located on another company drive, lets call it F. this file also can be moved locally if needed. Also, if we are going to hard-code the item# directly into the lines, then this master file may not be needed.

Worksheet:
the worksheet name in the first file is always identical to the name of the file, so the worksheet name for 2021120081 would just be 2021120081
the worksheet in the master file, is just sheet 1.

Ranges:
these are some of the example text we are looking for in column K
C BOX (6" WALL)
D BOX (6" WALL)
E BOX (6" WALL)
F BOX (6" WALL)
S BOX (6" WALL)
V BOX (6" WALL)
C BASE (6" WALL)
D BASE (6" WALL)
E BASE (6" WALL)
F BASE (6" WALL)

the numbers in column L can be anywhere between 12-96, here is an example of what I am trying to achieve, using D Box
Column K: D Box,6"w, Column L: 13". 13 is closest to 12, so the item# we will assign to column D will be F22132J, which is in cell B100 in master.xlsx, see image below
Column K: D Box,6"w, Column L: 26". 26 is closest to 24, so the item# we will assign to column D will be F22133J, which is in cell B101 in master.xlsx
Column K: D Box,6"w, Column L: 39". 39 is closest to 36, so the item# we will assign to column D will be F22134J, which is in cell B102 in master.xlsx
Column K: D Box,6"w, Column L: 50". 50 is closest to 48, so the item# we will assign to column D will be F22135J, which is in cell B103 in master.xlsx

1641383159096.png


let me know if this is enough infor, I apologize for the wall of text.
 

Attachments

  • 1641383085039.png
    1641383085039.png
    10.5 KB · Views: 4

kevin9999

Board Regular
Joined
Aug 28, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just to clarify Zack, is the sole ultimate objective here to get the correct item# from the master & put it into column D of the latest .csv file - or does the height from column L of the .csv also need to be appended to the description in column K (or was this just an intermediate step in the process)? Also, you say the numbers in column L can be as high as 96 - have you listed the complete range of possibilities in the master (12-72) in your screen shot? If not, I'll need all of them (numbers only - not text). Also, would there be an issue if you house the code in the master file?
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Just to clarify Zack, is the sole ultimate objective here to get the correct item# from the master & put it into column D of the latest .csv file - or does the height from column L of the .csv also need to be appended to the description in column K (or was this just an intermediate step in the process)? Also, you say the numbers in column L can be as high as 96 - have you listed the complete range of possibilities in the master (12-72) in your screen shot? If not, I'll need all of them (numbers only - not text). Also, would there be an issue if you house the code in the master file?
Kevin, the objectives are:
1. to modify the description (add structural heights to column K), which you helped me to finish in the other thread.
2. column D also need to be modified and the correct item# inputted.

Basically there are many types of structures, certain items can be taller than others, example:
the range for D box is 12-72" ( you can see this in one of the screenshots)
the range for F riser is 12-60", see below:

1641417099609.png


then there are items like the 5' dia risers, which have a max height of 96"
1641417300750.png
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Just to clarify Zack, is the sole ultimate objective here to get the correct item# from the master & put it into column D of the latest .csv file - or does the height from column L of the .csv also need to be appended to the description in column K (or was this just an intermediate step in the process)? Also, you say the numbers in column L can be as high as 96 - have you listed the complete range of possibilities in the master (12-72) in your screen shot? If not, I'll need all of them (numbers only - not text). Also, would there be an issue if you house the code in the master file?
there would not be any issues if certain codes need to be in the master file
 

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Just to clarify Zack, is the sole ultimate objective here to get the correct item# from the master & put it into column D of the latest .csv file - or does the height from column L of the .csv also need to be appended to the description in column K (or was this just an intermediate step in the process)? Also, you say the numbers in column L can be as high as 96 - have you listed the complete range of possibilities in the master (12-72) in your screen shot? If not, I'll need all of them (numbers only - not text). Also, would there be an issue if you house the code in the master file?
just want to add one more piece of information, this might be helpful.
even know there are many item#, but their format is consistent, and they all have a J at the end.
Like all C Box with 6: wall, the first few digits are always F2212
C Base with 6: wall, the first few digits are always F2112
C riser with 6: wall, 12"-72" tall, item# are F21222J, F21223J, F21224J, F21225J, F21226J, F21227J
C collar with 6: wall, same item# as the risers, except with a letter C at the end. like: F21222CJ, F21223CJ...etc
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,819
Messages
5,772,461
Members
425,760
Latest member
paphon

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