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:
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