VBA String Value Error

[lsteffen]

This the bolded row in my code is giving me a (Run-Time error '1004', Application-defined or Object-defined error)

'Set Supplier ID: Added 9/21/06 by lsteffen
Range("B2").Activate
strVal = "=IF(C2=805,7340,IF(C2=7,7741,IF(C2="",10000,0)))"
With Worksheets("COLLINSLOADS")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.Value = strVal
.Range("B2:B" & LastRow).FillDown
End With
Range("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Can someone tell me why? Is strVal to long?? The reason why I didn't put the IF statement in the spreadsheet is because each time this code is executed, a new spreadsheet gets created. I can send full code if needed.

Thanks,
Liz

 

[Smitty]

This may be a bit more flexible and will let you add cases:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#007F00">'Set Supplier ID: Added 9/21/06 by lsteffen</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> StrVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("C2").Value
            <SPAN style="color:#00007F">Case</SPAN> 805
                StrVal = 7340
            <SPAN style="color:#00007F">Case</SPAN> 7
                StrVal = 7741
             <SPAN style="color:#00007F">Case</SPAN> ""
                StrVal = 10000
             <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
                StrVal = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        
        <SPAN style="color:#00007F">With</SPAN> Worksheets("COLLINSLOADS")
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("B2:B" & LastRow) = StrVal
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[lsteffen]

I tried this and I got the dreaded "#VALUE". What I need is for the Supplier ID to populate based on the Sale ID. I am formatting a text file from a customer to upload into our system and the number of rows changes each time I get a new file. It could be 8 lines long or 99 lines long.

Liz