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]

failed to upload the first screenshot, here it is. Thanks!

 

[kevin9999]

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.

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]

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.

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]

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]

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

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

 

[kevin9999]

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]

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:

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

 

[zack8576]

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]

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