changing cell contents on basis off another cell

[hwfan123]

I am trying to get my code to input text into one cell by using the data in another cell as a guideline.
For Example in Column AE row 249 the cell data is ZN03, I want to change Column AK row 249 to display CAB1. I want the code to check all the cells in Row AE for this data and make changes in the corresponding columns. now for the part that I am having difficulty with, if the compare cell shows ZN04 I want the display cell to show SUBs and if it shows ZEPR I want the display cell to show EPROM.
Any suggestions on how to loop this correctly? Thanks iin advance for any help that is provided for this.
Nick

[Bob Phillips]

Not really sure, but pseudo code is



If compare_cell= "ZN04" Then
display_cell = "SUBs"
ElseIf compare_cell= "ZEPR" Then
display_cell = "EPROM"
End If

[hwfan123]

I am still really new to macros and I am learning as I go. with that said would I need to dim any variables and how would I have the code run through the entire column and make changes 4 columns over in the same row.

[mdmackillop]

Welome to VBAX

Option Explicit
Option Compare Text
Private Sub Test()
Dim cel As Range
For Each cel In Intersect(Rows(249), ActiveSheet.UsedRange)
Select Case cel
Case "ZN03"
cel.Offset(, 4) = "CAB1"
Case "ZN04"
cel.Offset(, 4) = "SUBs"
Case "ZEPR"
cel.Offset(, 4) = "EPROM"
End Select
Next
End Sub

[mdmackillop]

Rereading, I may have this the wrong way round. Are all the values in COLUMN AE or ROW 249. Please be careful with your use of these terms.

[Aussiebear]

I am trying to get my code to input text into one cell by using the data in another cell as a guideline.
For Example in Column AE row 249 the cell data is ZN03, I want to change Column AK row 249 to display CAB1. I want the code to check all the cells in Row AE for this data and make changes in the corresponding columns. now for the part that I am having difficulty with, if the compare cell shows ZN04 I want the display cell to show SUBs and if it shows ZEPR I want the display cell to show EPROM.
Any suggestions on how to loop this correctly? Thanks iin advance for any help that is provided for this.
Nick

I took this to read "For Example in AE249 the cell data is "ZN03", I want to change AK249 to display "CAB1". I want the code to check all the cells in Row AE for this data and make changes in the corresponding columns. now for the part that I am having difficulty with, if the compare cell shows "ZN04", I want the display cell to show "SUBs" and if it shows "ZEPR" I want the display cell to show "EPROM".

[mdmackillop]

For Column AE
For Each cel In Intersect(Columns(31), ActiveSheet.UsedRange)

[hwfan123]

Here is a sample of what I need the loop to look for/do. In the "Order Type" column I need the macro to search for the comments "ZEPR, ZN04, or ZN03". If this is found then two columns over in the "ver" column I need data input to be "Eprom, Subs, or CAB1" respectivly. This report is usually about 300 to 350 rows. I hope this helps clear things up. Sorry for any confusion. I am inputing the code that was supplied earlier and will let you know if that is the solution when I run it.
Thanks for all your help.

[mdmackillop]

Revised to suit your sample

Option Explicit
Option Compare Text
Private Sub Test()
Dim cel As Range
For Each cel In Intersect(Columns(6), ActiveSheet.UsedRange)
Select Case cel
Case "ZN03"
cel.Offset(, 2) = "CAB1"
Case "ZN04"
cel.Offset(, 2) = "SUBs"
Case "ZEPR"
cel.Offset(, 2) = "EPROM"
End Select
Next
End Sub

[Bob Phillips]

Public Sub Test()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
Select Case .Cells(i, "F").Value

Case "ZN03": .Cells(i, "H").Value = "CAB1"
Case "ZN04": .Cells(i, "H").Value = "SUBs"
Case "ZEPR": .Cells(i, "H").Value = "EPROM"
End Select
Next
End With
End Sub