VBA - Help with "For Each" statements with embedded "If Then" statements

RiffRiffington

New Member
Joined
Oct 1, 2018
Messages
7
Hello. I am brand new to VBA and also to this forum. Thanks to extensive searching and studying (mostly on this forum), I have managed to write my first several macros successfully. Thank you! The following code I am going to post here works successfully. However, I know that it is probably not the most efficient or best way of doing it. I ended up using cell references based on selections, and I know that it is better to avoid that kind of thing. I was wondering if anyone would be willing to tell me how I might be able to alter this one so that it functions without needing to reference selections.

The function of this macro is to search every cell in the range H7:H50 and look for a specific string "0000.000-000". If it finds that string in any of the cells in the range, it replaces it with a part number value located in cell A3, a hyphen, and then finally a sequence number that is found in the range A7:A50 in the corresponding cell. For example, if the macro finds the target string in cell H10, it will replace that cell with the string in cell A3 & - & the string found in cell A10. This code works, but I don't know how to get the sequence number in the A range to copy over to end of the corresponding cell in the H range without using the selection and offset commands as I have. This is my first post so hopefully, I have followed the rules and posted correctly. Please let me know if I need to clarify or do something differently. Thank you. Here is my VBA code:

Code:
Private Sub CommandButton1_Click()  'Autofill for the "Work Document Column:
                                    'User should select processes, fill in seq numbers, -
                                    'and put in P/N prior to clicking the button.


    Dim TargetString As String
    Dim TargetRange As Range
    Dim DMPN As Range
    Dim DMPN_Value As String
    Dim SeqRange As Range
    Dim SeqCheck As Integer
    Dim PN_Check As Integer
    Dim WI_Check As Double
    
    TargetString = "0000.000-000"
 
    Set DMPN = Range("A3")
    DMPN_Value = DMPN.value
    Set SeqRange = Range("A8:A50")
    SeqCheck = SeqRange.Count - WorksheetFunction.CountBlank(SeqRange)
    PN_Check = DMPN.Count - WorksheetFunction.CountBlank(DMPN)
    WI_Check = Range("H7:H50").Count - WorksheetFunction.CountIf(Range("H7:H50"), TargetString)
    
            If SeqCheck = 0 Then 'Checks if sequence numbers have been filled in before running.
                MsgBox ("Enter sequence numbers before auto-filling Work Document references.")
                    Exit Sub
            End If
            
            If PN_Check = 0 Then 'Checks if P/N has been filled in before running.
                MsgBox ("Enter P/N in cell A3 before auto-filling Work Document references.")
                    Exit Sub
            End If
            
            If WI_Check > 43 Then 'Checks if any processes contain the proper Work Instruction format.
                MsgBox ("No processes currently contain the '0000.000-000' Work Instruction format.")
                    Exit Sub
            End If
    
        Cells(7, 1).Select 'Selects first Seq No
    
    For Each TargetRange In Range("H7:H50")
       
            If TargetRange.value = TargetString Then
                TargetRange = DMPN_Value & "-" & ActiveCell.value
            End If
            
        ActiveCell.Offset(1, 0).Select
        
    Next
    
    Cells(7, 1).Select 'Returns selection to first Seq No
           
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
How about
Code:
targetrange = DMPN_Value & "-" & targetrange.Offset(, -7).Value
 
Upvote 0
Try this:
My script does what you said here:
Not sure what all this other code in your script is for.

You said:

The function of this macro is to search every cell in the range H7:H50 and look for a specific string "0000.000-000". If it finds that string in any of the cells in the range, it replaces it with a part number value located in cell A3, a hyphen, and then finally a sequence number that is found in the range A7:A50 in the corresponding cell


Code:
Sub Search_Me()
Application.ScreenUpdating = False
Dim r As Range
'Modified  10/1/2018  5:06:33 PM  EDT
    For Each r In Range("H7:H50")
        If r.Value = "0000.000-000" Then r.Value = Range("A3").Value & "-" & r.Offset(, -7).Value
    Next
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Try this:
My script does what you said here:
Not sure what all this other code in your script is for.

Code:
Sub Search_Me()
Application.ScreenUpdating = False
Dim r As Range
'Modified  10/1/2018  5:06:33 PM  EDT
    For Each r In Range("H7:H50")
        If r.Value = "0000.000-000" Then r.Value = Range("A3").Value & "-" & r.Offset(, -7).Value
    Next
Application.ScreenUpdating = True
End Sub

The other code is simply some steps to create warning messages if a user tries to use the macro improperly or doesn't input the necessary information first. But your suggestion worked. Thank you much!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
The other code is simply some steps to create warning messages if a user tries to use the macro improperly or doesn't input the necessary information first. But your suggestion worked. Thank you much!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top