Start Free Trial

asked on

Double if statement in a While loop

The following code places a value in a cell within a while loop, however right after this I need to perform a check that I wrote in English.
Need assistance writing the extension to this process.

The process is going to be used to chg and table lookup value, before it is looked up.

'Place (STGFAB, STGMESH,SCT?? in first blank cell after offset 6
        ColOffset = 6
        Do
            ColOffset = ColOffset + 1
        Loop Until c.Offset(0, ColOffset).Value = ""
        c.Offset(0, ColOffset).Value = SurfCH_ValueAndSurfCH_FixedValue

I
'    Need to check the value of SurfCH_ValueAndSurfCH_FixedValue or
'    active cell, and write an if statement,
'    if it equals "STGMESH"?    if anything else continue
'    Goto first column of c
'    Second if, if begins with "SZ",
'    Goto sheet tab. FinGrpFinUsedTable
'    Find that value in column E,
'    Chg the cell to the right of that to ",MA_1"

Open in new window

What value are you testing for the leftmost characters equalling "SZ"?
1) SurfCH_ValueAndSurfCH_FixedValue
2) c.value (aka c.offset(0,0))

If 1):

    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        'do something?
    ElseIf Left(SurfCH_ValueAndSurfCH_FixedValue, 2) = "SZ" Then
        Sheets("FinGrpFinUsedTable").Select
        Set rng = Range("E2", Range("E2").End(xlDown))
        For Each cl In rng
            If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                cl.Offset(0, 1).Value = ",MA_1"
            End If
        Next cl
    End If

Open in new window

If 2):

    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        If Left(c.Value, 2) = "SZ" Then
            Sheets("FinGrpFinUsedTable").Activate
            Set rng = Range("E2", Range("E2").End(xlDown))
            For Each cl In rng
                If cl.Value = c.Value Then
                    cl.Offset(0, 1).Value = ",MA_1"
                End If
            Next cl
        End If
    End If

Open in new window

I'm not completely sure of your instructions, so these are just starting points.

-Glenn

ASKER

We are looking at option 1. SurfCH_ValueAndSurfCH_FixedValue is in a cell off to the right in the worksheet, can be column K, L, M, N..... etc. I somehow need to get over to column A of the row we are in from the While loop, to check the first two chars of that, so it is another if statement. First if it is "STGMESH" in the active cell, and another on the first two chars of value in column A of whatever row, is being looped through. Hope this help explain it a little better.

ASKER

The
If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
'do something?

The something is what appears to be in the If Else? If it is not "STGMESH" it will goto the next if. If all are false it will continue as normal

I will have more cases in this, like if first two chars are "SZ" set to MA_1
If first two are SL set the table value to MS_BL etc. I need to get this to work for one first before I add the other cases but thought that information may help.

ASKER

I recorded just pressing the [Home] key and it recorded the following:
ActiveCell.Offset(0, -8).Range("A1").Select

but it is not always going to be -8 offset, it can be -8, -9, -11 etc, depending on the data that is gathered in that row. There another way to simulate the [Home] key to get it to the cell that I need to check the first two chars?

Not sure if I need to get it back to a specific cell, after or not but will look at that after the if statement checks.

Actually, from your last two posts, it sounds like we're looking at option 2.

Since you're starting your loop with an offset of 7 (increments 6 by 1 at the start of the loop), and column K is the first possible blank column, then it sounds like "c" is located in column D. Is that correct? If so, I think this code is more like it:

Sub test()
    Dim SurfCH_ValueAndSurfCH_FixedValue As String
    Dim rng As Range
    Dim cl As Object
    Dim strTest As String

    'This code adds SurfCH.. to the first available blank cell
    ColOffset = 6
    Do
        ColOffset = ColOffset + 1
    Loop Until c.Offset(0, ColOffset).Value = ""
    c.Offset(0, ColOffset).Value = SurfCH_ValueAndSurfCH_FixedValue

    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        strTest = c.Offset(0, -3).Value 'if c is in col D, offset-3 is col A
        If Left(strTest, 2) = "SZ" Then
            Sheets("FinGrpFinUsedTable").Activate
            Set rng = Range("E2", Range("E2").End(xlDown))
            For Each cl In rng
                If cl.Value = strTest Then
                    cl.Offset(0, 1).Value = ",MA_1"
                End If
            Next cl
        End If
    End If
End Sub

Open in new window

Note that this is wrapped in a subroutine (test) just to help check variable assignment and outline levels.

-Glenn

ASKER

I am seeing issues with Ln15, it fails the test if = to "SZ". strTest is = "". I do not think it is getting back to column A to test that value correctly. Still testing.

The offset of -3 will not be the same each time. I debated about doing a series of Ctrl+left's to get it over there? Actual two would do it.

Not sure how to figure out how many offset c to get there, or what it started on it set it. It is looking like c is column A. Checking.

I need to see the code that preceeds the Loop...Until method in lines 9-12, especially anything that defines where the object c is located.

Based on your statements that "SurfCH...." variable could be in column K onward, the first possible blank column would be seven (7) columns offset to the right of c. Since column K is the 11th column, it means that c is in column D.

And the negative offset would be the same everytime; it's value just depends on where the initial offset column is.

This is a good example as to why it's helpful to show more-complete code and sample data (redacted, if necessary).

-Glenn

ASKER

Ok I may have stated the column K thing wrong. Here is the full procedure. The part that this would fit in is marked as: EE Question sub here. Perhaps you can see better that I am trying to get into the table and change a value that will be looked up, prior to looking it up in the table, based on the two if statements. Can you tell where .c is defined? I think it is column A?

Sub GetMatlNumberFromCatCode() 'Ctrl+j  Assumes SAP>ZEC has an open session

'check for SAP session open
sessChoice = "ZEC"  'variables used by TESTSAP function     'RANDY:  set these to PEC if so desired.
connChoice = "710"
If TestSAP = False Then Exit Sub        'The TESTSAP function will make your connection between VBA and SAP!!!!!!!

For Each c In Selection  'C is a list is hightlighted in excel
If c.Value = "" Then Exit For  'when end of list is reached, exit. no End If with the Then statement

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzgmgr001"
session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtP_CATCOD-LOW").Text = c.Value
session.findById("wnd[0]/usr/chkP_CHK1").Selected = False
session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/tbar[1]/btn[45]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press

    Sheets("Translation").Select
    Cells.Select
    Selection.ClearContents
    
    Range("A1").Select
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
If Range("B6") = "" Then  'if nothing in cell B6, do TextToColumns
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1)), TrailingMinusNumbers:=True
End If

'remove the spaces from ColO and ColX so if statement will work.
'not sure if more need to be added.
       Range("O:O,X:X,Q:Q,M:M,W:W").Select
       Range("X1").Activate
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("o6").Select
''********************************
'Kill old translations sub. Assumes old ones are on top.
Call DelOOETranslations

'Collect Surface FinishGrp info for each "S" in translation.
'start in cell O6, Do until not equal to "S"
    Do While ActiveCell = "S"
       ActiveCell.Offset(0, 9).Select 'move to FinGrp column
       If ActiveCell = "" Then
'************************************
'Assumes "S" and TrimGrpCode blank
ActiveCell.Offset(0, -1).Select 'move to CH_ column
SurfCH_Value = ActiveCell.Value 'Names Active SurfCH_Value to find in column M
ActiveCell.Offset(0, -10).Select 'move to CH_ column
CH_SurfFinGrp = Mid(ActiveCell.Value, 4, 3)
'Looking for value stored in SurfCH_Value in ColM starting at row 6
    Dim CH_FixedValueForSurf
    Dim rng As Range
    Dim z As Range

    Set rng = Range("M6:M" & Range("M6").End(xlDown).Row)
    For Each z In rng
        If z.Value = SurfCH_Value Then
          ' Here you asign the value to the "CH_FixedValueForSurf" variable
            CH_FixedValueForSurf = z.Offset(0, 4).Value
            Exit For
        End If
    Next
'concatinate two values
SurfCH_ValueAndSurfCH_FixedValue = CH_SurfFinGrp & CH_FixedValueForSurf

'Reset for next "S"

'       Navigate to the surface TrmGrp cell in row

'       From table, base on concatinated value place associated value in next cell
'       Go back to marker, or start next "S"
            
            Sheets("MasterCopy").Select


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Place (STGFAB, STGMESH,SCT?? in first blank cell after offset 6
        ColOffset = 6
        Do
            ColOffset = ColOffset + 1
        Loop Until c.Offset(0, ColOffset).Value = ""
        c.Offset(0, ColOffset).Value = SurfCH_ValueAndSurfCH_FixedValue

'EE Question sub here
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&
'
''    Dim SurfCH_ValueAndSurfCH_FixedValue As String   'already set as string
''    Dim rng As Range  'already set as Range, or needs to be renamed.
'    Dim cl As Object
'    Dim strTest As String
'
'    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
'        strTest = c.Offset(0, -3).Value 'if c is in col D, offset-3 is col A
'        If Left(strTest, 2) = "SZ" Then
'            Sheets("FinGrpFinUsedTable").Activate
'            Set rng = Range("E3", Range("E3").End(xlDown))
'            For Each cl In rng
'                If cl.Value = strTest Then
'                    cl.Offset(0, 1).Value = ",MA_1"
'                End If
'            Next cl
'        End If
'    End If
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'    Need to check the value of SurfCH_ValueAndSurfCH_FixedValue or
'    active cell, and write an if statement,
'    if it equals "STGMESH"?    if anything else continue
'    Goto first column of c
'    Second if, if begins with "SZ",
'    Goto sheet tab. FinGrpFinUsedTable
'    Find that value in column E,
'    Chg the cell to the right of that to ",MA_1"
'    Return to sheet tab "MasterCopy"

'Look up value in table on FinGrpFinUsedTable sheet tab
Sheets("FinGrpFinUsedTable").Select  'goto sheet with tables in it
    'PatternBasedFinishToUse is the new name of the value found in table
    'SurfCH_ValueAndSurfCH_FixedValue is the value assigned above
    'PatternBasedFinTable is the tablename in FinGrpFinUsedTable sheet tab
    
    PatternBasedFinishToUse = WorksheetFunction.VLookup(SurfCH_ValueAndSurfCH_FixedValue, Range("PatternBasedFinTable"), 2, 0)
    Sheets("MasterCopy").Select
'   find the first open cell in .c and put the value there.
    ColOffset = 6
        Do
            ColOffset = ColOffset + 1
        Loop Until c.Offset(0, ColOffset).Value = ""
        c.Offset(0, ColOffset).Value = PatternBasedFinishToUse 'from table
'static positioning of the value
'            C.Offset(0, 5).Value = "NoGrpCode" 'assumes current trans not OOE
'            C.Offset(0, 6).Value = "NoFin"

         Sheets("Translation").Select
'Send back to start
         ActiveCell.Offset(0, 11).Select  'go back and check again.
       End If
         
If ActiveCell = "" Then
    GoTo 123
End If

If Not ActiveCell = "" Then CH_FinGrp = ActiveCell.Value

'    If Not ActiveCell = "" Then CH_FinGrp = ActiveCell.Value
      Sheets("MasterCopy").Select
'       C.Offset(0, 5).Value = CH_FinGrp 'places value on MasterCopy sheet
'Place it in the first blank cell it can find after offset 5
    ColOffset = 6
        Do
            ColOffset = ColOffset + 1
        Loop Until c.Offset(0, ColOffset).Value = ""
    c.Offset(0, ColOffset).Value = CH_FinGrp

'    CH_FinGrp = ActiveCell.Value
    Sheets("FinGrpFinUsedTable").Select
    
On Error GoTo 100  'This will kill the For Loop and stop macro
'    ActualFinishToUse = WorksheetFunction.VLookup(CH_FinGrp, Range("A1:C18"), 2, 0)
' This Debug error means that a FinGrpCode was not in the workbook table

    ActualFinishToUse = WorksheetFunction.VLookup(CH_FinGrp, Range("FinGrpFinCodeTable"), 2, 0)
    
    
    Sheets("MasterCopy").Select
'' Finds the first available blank to the right
    ColOffset = 6
        Do
            ColOffset = ColOffset + 1
        Loop Until c.Offset(0, ColOffset).Value = ""
    c.Offset(0, ColOffset).Value = ActualFinishToUse  'Refers to value assigned above
    
''   Autofilters ColB or 2 based on saved value CH_FinGrp   **KEEP**
'    Sheets("FinishGrpCodes").Select
'    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=CH_FinGrp
'    If continue down this path need to take this list of FinishCodes
'    and determine what grade it is because not all codes in a grp are same grade



'now that FinGrp is there
'need to sort sheet by that value
'randonly pick a finish from results
'place that in the cell next to the fingrp

'filters another sheet based on a collected value from another sheet
'do not need this anymore but good option also
'    Sheets("FinishGrpCodes").Select
'    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=CH_FinGrp

'now that FinGrp is there
'need to sort sheet by that value
'randonly pick a finish from results
'place that in the cell next to the fingrp
       
123  'For TrmGrpBased
'goto the next one if there is one.
       Sheets("Translation").Select
       ActiveCell.Offset(1, -9).Select  'go back and check again.
    
    Loop
        
        
'Translation sheet tab still active sheet set some values to names
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[14],""S"")"
'Set formulas to look for Surf_Type
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""WD*"")=0,"""",""WD"")"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""LAM*"")=0,"""",""LAM"")"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""FAB*"")=0,"""",""FAB"")"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""PTD*"")=0,"""",""PTD"")"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""MET*"")=0,"""",""MET"")"
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(C[16],""GLZ*"")=0,"""",""GLZ"")"
    'define all the values above
    NumberOfSurf = Range("A6").Value
    MatlTypeOfWD = Range("A7").Value
    MatlTypeOfLAM = Range("A8").Value
    MatlTypeOfFAB = Range("A9").Value
    MatlTypeOfPTD = Range("A10").Value
    MatlTypeOfMET = Range("A11").Value
    MatlTypeOfGLZ = Range("A12").Value
    MatlNumber = Range("H6").Value
    CatCodeDesc = Range("F6").Value
    'place all the defined values on the row being reviewed
    Sheets("MasterCopy").Select
       c.Offset(0, 4).Value = MatlNumber  'places whatever was in Translation H6 into MasterCopy sheet, 2 right of CatCode selected
       c.Offset(0, 5).Value = CatCodeDesc
       c.Offset(0, 6).Value = NumberOfSurf
       c.Offset(0, 30).Value = MatlTypeOfWD  'ColU
       c.Offset(0, 31).Value = MatlTypeOfLAM  'ColV
       c.Offset(0, 32).Value = MatlTypeOfFAB  'ColW
       c.Offset(0, 33).Value = MatlTypeOfPTD  'ColX
       c.Offset(0, 34).Value = MatlTypeOfMET  'ColY
       c.Offset(0, 35).Value = MatlTypeOfGLZ  'ColZ
       
' need to rewrite the formula to cancatinate finishes produced from above.
       c.Offset(0, 2).FormulaR1C1 = "=RC[-2]&RC[6]&RC[8]&RC[10]&RC[12]&RC[14]&RC[16]"

'Check MESH? here.  What is base one?  If base one this then that?
'Add this inside the For loop to address each line in translation separately

'Call to OverRuleWDwithOneSurf value
'Commented out 8-7-14 RWH  Wrote new PatternBased sub to cover this
' If MatlTypeOfWD = "WD" And NumberOfSurf = "1" Then
'      c.Offset(0, 8).Value = ",WL_RL" 'has to be position 8 for one surf fin
' End If
' If MatlTypeOfFAB = "FAB" And NumberOfSurf = "1" Then
'      c.Offset(0, 8).Value = ",3A_18" 'has to be position 8 for one surf fin
' End If

Next c

GoTo 202
100
    MsgBox ("FinGrp in the Translation sheet not in FinGrpFinCodeTable")
    Exit Sub
Resume Next
202



End Sub

Open in new window

Line 8 states
For Each c in Selection

What column is being selected? There's no indication here and the selection has to be made before this macro is run.

ASKER

Column A is where the selection is made.

ASKER

I highlight the selection range in column A

Hi,

This is untested air-code but to always return or apply a value to column A you can try:

C.entirerow.cells(1,1).value

Hth
Rob

I've been going over this code on and off for a couple of days. In addition to the difficulty in not seeing a sample worksheet that this code is working on, the multiple use of Select and Offset statements makes it difficult to follow. Even with the abundance of comments, it does not always make sense.

For example (lines 55-71 of original code)

'Collect Surface FinishGrp info for each "S" in translation.
'start in cell O6, Do until not equal to "S"
    Do While ActiveCell = "S"
       ActiveCell.Offset(0, 9).Select 'move to FinGrp column
       If ActiveCell = "" Then
'************************************
'Assumes "S" and TrimGrpCode blank
ActiveCell.Offset(0, -1).Select 'move to CH_ column
SurfCH_Value = ActiveCell.Value 'Names Active SurfCH_Value to find in column M
ActiveCell.Offset(0, -10).Select 'move to CH_ column
CH_SurfFinGrp = Mid(ActiveCell.Value, 4, 3)
'Looking for value stored in SurfCH_Value in ColM starting at row 6
    Dim CH_FixedValueForSurf
    Dim rng As Range
    Dim z As Range

    Set rng = Range("M6:M" & Range("M6").End(xlDown).Row)

Open in new window

The comment states that this loop starts in cell O6. If true, the Offset(0, 9) statement should place it in column X to test for a blank. If so, SurfCH_Value should be the associated value in column W (one column to the left). and CH_SurfFinGrp should be a substring of the associated value in column M. But the comments in lines 62-64 do not corroborate this and are not helpful.

In my opinion, the select/offset methods need to be replaced and reduced. Additionally, a sample worksheet would be extremely helpful in seeing what the sample data looks like and how it is to be manipulated.

In the meantime, I've attached an example file with some cleaned up coding and dimensioning of variables. I may not have the proper type declarations for the "Matl..." group since I don't know what the actual data looks like.
EE-Q-28506457.xlsm

ASKER

That Do loop is inside the For Selection Ln 8 and 9. It refers to a different sheet called Translation. The For Selection is where it starts, and in column A, perhaps we do not have offset anything off in:

strTest = c.Offset(0, -3).Value 'if c is in col D, offset-3 is col A

Open in new window

I need to figuring out what cell strTest is pointing to is the question

ASKER

Disregard my last post... I was thinking.
Perhaps we could approach it a different way... by rewriting:

  If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        strTest = c.Offset(0, -3).Value 'if c is in col D, offset-3 is col A
        If Left(strTest, 2) = "SZ" Then

Open in new window

to this, in English.
If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" and the first two characters of cell B6 of the "Translation" sheet = "SZ" Then

I am just not sure if I can check a cell of an inactive sheet? Actual not sure what sheet is active at that time.
The cell that we are trying to offset to and check, is in a static cell of the Translation sheet in cell B6, after the SAP extract, so we really do not have to offset back anywhere. If we could go directly to a static cell and check, that would be a lot easier.

I was thinking that this maybe an easier way to get the true statement of this.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks, let me test this.

ASKER

Ok we are really close now.... please hang with me. First If is True, and works, second if is true and works, however when it goes to FinGrpFinUsedTable sheet tab, it did not find the STGMESH value which is there to replace whatever value it there with MA_1 ??? Any idea why it did not find it? They are both now reading as True statements... yahoo
I did loop through cl, but it appeared to not find "STGMESH" in the list. Which is the result of SurfCH_ValueAndSurfCH_FixedValue

The code used is:

    Dim cl As Object
    Dim strTest As String

    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        If Left(Sheets("Translation").Range("B6").Value, 2) = "SZ" Then
            Sheets("FinGrpFinUsedTable").Activate
            Set rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In rng
                If cl.Value = strTest Then
                    cl.Offset(0, 1).Value = ",MA_1"
                End If
            Next cl
        End If
    End If

Open in new window

ASKER

Got it!!! just chg'ed strTest to SurfCH_ValueAndSurfCH_FixedValue
If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then

EXCELent!! One last favor before we close this, can I have another If condition?
In the statement:

 If Left(Sheets("Translation").Range("B6").Value, 2) = "SZ" Then

Open in new window

How would I ask it, if first 2 characters are SZ and the 8th character is a "W"? This will finish this issue. Thanks.

ASKER

So here is the solution... How would I add something to the end of this, that says if it is not either of these to scenarios? Set the value to a default.. ABC_123?

    Dim cl As Object
    
'If = STGMESH and Zody (SZ), make sure back mesh is MA_1
    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        If Left(Sheets("Translation").Range("B6").Value, 2) = "SZ" Then
            Sheets("FinGrpFinUsedTable").Activate
            Set rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In rng
                If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                   cl.Offset(0, 1).Value = ",MA_1"
                End If
            Next cl
        End If
    End If
    
'If = STGMESH and Improv SE (M), make sure back mesh is WO_1
    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        If Left(Sheets("Translation").Range("B6").Value, 1) = "M" Then '1st ch_ is a M
            Sheets("FinGrpFinUsedTable").Activate
            Set rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In rng
                If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                   cl.Offset(0, 1).Value = ",WO_1"
                End If
            Next cl
        End If
    End If

Open in new window

How about this code?

    Dim cl As Object
    
    If SurfCH_ValueAndSurfCH_FixedValue = "STGMESH" Then
        'if Zody (SZ) - back mesh is MA_1
        If Left(Sheets("Translation").Range("B6").Value, 2) = "SZ" Then
            Sheets("FinGrpFinUsedTable").Activate
            Set Rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In Rng
                If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                   cl.Offset(0, 1).Value = ",MA_1"
                End If
            Next cl
        ElseIf Left(Sheets("Translation").Range("B6").Value, 1) = "M" Then '1st ch_ is a M
            'if Improv SE (M), make sure back mesh is WO_1
            Sheets("FinGrpFinUsedTable").Activate
            Set Rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In Rng
                If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                   cl.Offset(0, 1).Value = ",WO_1"
                End If
            Next cl
        Else 'set back mesh to default value ABC_123
            Sheets("FinGrpFinUsedTable").Activate
            Set Rng = Range("E3", Range("E3").End(xlDown))
            For Each cl In Rng
                If cl.Value = SurfCH_ValueAndSurfCH_FixedValue Then
                   cl.Offset(0, 1).Value = ",ABC_123"
                End If
            Next cl
        End If
    End If

Open in new window

-Glenn

ASKER

I will check and test this. Thanks I will let you know how it goes.

ASKER

Thanks for hangin with me on this one. This worked perfectly... I had a tough time explaining this but you followed it nicely. Really appreciate the help.

You're welcome. Was hard to debug without seeing actual worksheets.

-Glenn