#### joshua sneddon

##### New Member

- Joined
- Oct 1, 2014

- Messages
- 3

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>