Help fill down numbers based on value of another cell

[joshua sneddon]

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>

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>

 

[jasonb75]

Welcome to the board!

What you're trying to do is making sense, but not how you get your results from the source data.

What is the relevance of "R" in "R" & Sheets("1").....etc? There is no "R" in your sample table.

Your code indicates that "M999" is in C9, but where do the other values, "M111", "M444", etc come from?

How is the second part, "MF", "TC", etc decided?

I there anything to say which "stjb no's" should be matched with "M999", which with "M444", etc?

 

[joshua sneddon]

Welcome to the board!

What you're trying to do is making sense, but not how you get your results from the source data.

What is the relevance of "R" in "R" & Sheets("1").....etc? There is no "R" in your sample table.

Your code indicates that "M999" is in C9, but where do the other values, "M111", "M444", etc come from?

How is the second part, "MF", "TC", etc decided?

I there anything to say which "stjb no's" should be matched with "M999", which with "M444", etc?

Good questions I'll try to answer them.

So the numbers in M999, M111 and M444 are decided by the user input they enter on sheet(1) they enter then Number then the letter is assigned by the first "R" in the formula. If that makes sense. This also explains the relevance of "R" in your first point, it is simply a letter assigned to the number depending on the st jb numbers.

The second part "MF", "TC", etc are decided by the stjb no's.

I'll try to break down the unique identifier a bit more. This is what the finished product looks like R999-TC-001. The Prefix letter (in this sample R) is assigned by the stjb no's (standard job numbers). the numbers following (999) are user input in sheet1. the middle 2 letters are decided by the standard job number. then the last 3 numbers should begin at 1 and count up for each of the other bits of information used. so there are no duplicate numbers.

Hopefully this cleared up some of the issues.

 

[jasonb75]

I'm still not clear how you decide which of the various bits tie up to which job number. By writing the job numbers into the code as you have done, the code will always execute on the same lines unless you edit the code.

For example, user enters 999 in C9, then the code will run "R999-TC-00x" on the specified job numbers, if they change C9 to 444, then the code will still be running the same job numbers, overwriting 999 from the previous run.

To make the code work properly, we need to establish how to identify the correct job numbers that we should be working with, without entering them into the code.

As a quick example,

Option Explicit
Sub tancnumber()
Dim c As Range, str As String
Dim TCcounter As Long, MFcounter As Long
str = "R" & Sheets("1").Range("C9").Value
With Sheets("2")
    For Each c In Intersect(.Range("E:E"), .UsedRange)
        Select Case .Value
            Case "SP0148", "SP0239"
                c.Offset(0, 8) = str & "-TC-" & TCcounter
                TCcounter = TCcounter + 1
            Case "SP0218", "SP0220"
                c.Offset(0, 8) = str & "-MF-" & MFcounter
                MFcounter = MFcounter + 1
        End Select
    Next
End With
End Sub

But, as with your original code, you would need to rewrite it every time you want to use it on different job numbers.

 

[joshua sneddon]

I am getting runtime error 438, object does not support this property or method. I have inserted the TCcounter TCcounter = TCcounter + 1 into the formula i have used and it works, though refferbly the number would always have 3 digits? att he moment it is returning 1,2,3 etc where it would be nice if it showed 001,002,003 etc. This is not a sticking point and if impossible I'm sure it will be ok.

Your code looks alot neater than mine as if i have 50 standard job numbers it gets a bit ridiculous where as with yours it would keep it nice. So I am still interested in trying to make it work and am trying if you could help with the Error 438.

Your point "To make the code work properly, we need to establish how to identify the correct job numbers that we should be working with, without entering them into the code." i'm not quite sure i understand this i have thought about having a sort of database tab so the code is referring to a cell and in that cell the standard job numbers?

 

[jasonb75]

This fixes the RunTime error and the formatting.

Option Explicit
Sub tancnumber()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual

Dim c As Range, str As String
Dim TCcounter As Long, MFcounter As Long
str = "R" & Sheets("1").Range("C9").Value
With Sheets("2")
    For Each c In Intersect(.Range("E:E"), .UsedRange)
        Select Case c.Value
            Case "SP0148", "SP0239"
                TCcounter = TCcounter + 1
                c.Offset(0, 8) = str & "-TC-" & Format(TCcounter, "000")
            Case "SP0218", "SP0220"
                MFcounter = MFcounter + 1
                c.Offset(0, 8) = str & "-MF-" & Format(MFcounter, "000")
        End Select
    Next
End With

    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

With regard to making it work properly, or more correctly, to make it work effectively, how do you decide which job numbers that the code should be applied to?

So going back to your original samples

SA0015 M999-MF-001
SP0217 M444-TC-001
SM0001 M999-MF-002
ST0011 M999-MF-003
SA0013 M444-TC-002
SQ0015 M111-LT-001
MK0317 M444-TC-003

Why SA00013 M444 but SA00015 M999, why not both M999, or the other way around? What is the criteria for you making that decision?
Similarly, how are do you decide on TC or LT?