joshua sneddon
New Member
- Joined
- Oct 1, 2014
- Messages
- 3
Hi, sorry I am pretty new at this any help would be greatly appreciated. I have a VBA code which is possibly over complex but it does the job except for one small detail. Column E is a letter and number combination which signifies the type of work that is to be completed (e.g. SA0015 or SP0217). Depending on this number I would need column M to be a unique identifier Which would be something like M999-MF-001 with the letters MF being reliant on the value of Column E (there are 3 variants which these letters could be) and the suffix 001 having 1 added to it each time that ‘unique identifier has been used. The Prefix M999 is a user input which is entered into another worksheet before it is run. </SPAN>
I have been playing around with "" & "" & etc formula but am struggling to get the filldown numbers to work properly.
Sub tancnumber()
Dim x As Long
For x = 1 To 65536
If InStr(1, Sheets("2").Range("$e$" & x), "SP0148") > 0 Or InStr(1, Sheets("2").Range("$e$" & x), "SP0239") > 0 Then ' searches the two stjb no.s in column e
Sheets("2").Range("$m$" & x) = "R" & Sheets("1").Range("c9") ' this is the user input information & "-TC-" & "001" ' the 001 is where i'm having all the trouble
'i then essentially repeat for all possible work type numbers
ElseIf InStr(1, Sheets("2").Range("$e$" & x), "SP0218") > 0 Or InStr(1, Sheets("2").Range("$e$" & x), "SP0220") > 0 Then ' searches the two stjb no.s in column e
Sheets("2").Range("$m$" & x) = "R" & Sheets("1").Range("c9") & "-TC-" & "001"
End If
Next
End Sub
There is a different amount of data each week.
</SPAN>
Below is a sample of the data and required result, excluding irrelevant columns.</SPAN>
<TBODY>
</TBODY>
Hopefully that all made sense and I can get some help</SPAN>
I have been playing around with "" & "" & etc formula but am struggling to get the filldown numbers to work properly.
Sub tancnumber()
Dim x As Long
For x = 1 To 65536
If InStr(1, Sheets("2").Range("$e$" & x), "SP0148") > 0 Or InStr(1, Sheets("2").Range("$e$" & x), "SP0239") > 0 Then ' searches the two stjb no.s in column e
Sheets("2").Range("$m$" & x) = "R" & Sheets("1").Range("c9") ' this is the user input information & "-TC-" & "001" ' the 001 is where i'm having all the trouble
'i then essentially repeat for all possible work type numbers
ElseIf InStr(1, Sheets("2").Range("$e$" & x), "SP0218") > 0 Or InStr(1, Sheets("2").Range("$e$" & x), "SP0220") > 0 Then ' searches the two stjb no.s in column e
Sheets("2").Range("$m$" & x) = "R" & Sheets("1").Range("c9") & "-TC-" & "001"
End If
Next
End Sub
There is a different amount of data each week.
</SPAN>
Below is a sample of the data and required result, excluding irrelevant columns.</SPAN>
E</SPAN> | M</SPAN> |
SA0015</SPAN> | M999-MF-001</SPAN> |
SP0217</SPAN> | M444-TC-001</SPAN> |
SM0001</SPAN> | M999-MF-002</SPAN> |
ST0011</SPAN> | M999-MF-003</SPAN> |
SA0013</SPAN> | M444-TC-002</SPAN> |
SQ0015</SPAN> | M111-LT-001</SPAN> |
MK0317</SPAN> | M444-TC-003</SPAN> |
<TBODY>
</TBODY>
Hopefully that all made sense and I can get some help</SPAN>