referring to numbers/alphabets

[Anomandaris]

A cell value can be either 5 digit number like 35566 or 4 numbers and 1 letter like '3344D'

How do i right an if then statement?

If A1 Like '####?' Then .........

the first 4 are always numbers, just the last chracter can change

please help

thanks

[Bob Phillips]

If Range("A1").Value Like "####?" Then

[Anomandaris]

Thanks xld but it doesnt work, maybe it has to do with the code following the If then statement:


I've highlighted the part that shows up as an error (the code works for ##### but if I use ####? then i get an error

Basically what my code does is check Column A for a 5 digit account number then sends some figures to a worksheet which is named by the Account number( 5 digit number)

dont understand why it isnt working though


Dim ws As Worksheet, rData As Range, n As Long, rCell As Range
Dim wsSource As Worksheet

Application.ScreenUpdating = False
Set wsSource = Sheets("Detail")
With wsSource
Set rData = .Range("A2", .Range("Z" & Rows.Count).End(xlUp))
End With

For Each ws In Worksheets
With ws
If ws.Name Like "####?" Then
With .Cells(Rows.Count, 1).End(xlUp)(2)
.Value = wsSource.Cells(2, 3).Value

.Offset(, 1).Resize(, 13).Formula = _
"=SUMPRODUCT((Detail!" & rData.Columns(1).Address & "=" & ws.Name & ")*(Detail!" & rData.Columns(26).Address & "=B2)*(Detail!" & rData.Columns(24).Address & "))"
.Offset(, 1).Resize(, 13).Value = .Offset(, 1).Resize(, 13).Value
End With
End If
End With
Next ws
line1:
Application.ScreenUpdating = True

End Sub

[Bob Phillips]

Dim ws As Worksheet, rData As Range, n As Long, rCell As Range
Dim wsSource As Worksheet

Application.ScreenUpdating = False
Set wsSource = Sheets("Detail")
With wsSource
Set rData = .Range("A2", .Range("Z" & Rows.Count).End(xlUp))
End With

For Each ws In Worksheets
With ws
If ws.Name Like "####?" Then
With .Cells(Rows.Count, 1).End(xlUp)(2)
.Value = wsSource.Cells(2, 3).Value

.Offset(, 1).Resize(, 13).Formula = _
"=SUMPRODUCT((Detail!" & rData.Columns(1).Address & "=""" & ws.Name & """)*(Detail!" & rData.Columns(26).Address & "=B2)*(Detail!" & rData.Columns(24).Address & "))"
.Offset(, 1).Resize(, 13).Value = .Offset(, 1).Resize(, 13).Value
End With
End If
End With
Next ws
line1:
Application.ScreenUpdating = True

End Sub

[Anomandaris]

thanks xld --- it works for the '3355D' case
but it doesnt work for the 5 digit number case, for eg if its 33444 the code doesnt do the SUMproduct and copy into that page

[Bob Phillips]

That is because you are testing for a sheetname like ####?

[Anomandaris]

I see, i guess its not a good way to do it. I named the worksheets exactly the same as account numbers so it would match it and send the data there.
I'll post an example tomorrow, maybe you can see where its going wrong for me.....

thanks, and sorry i shouldve posted a workbook to begin with, just makes things easier

[Anomandaris]

I've changed this part, and it is working so far. I'm just using 2 separate If-then statements for the 2 different types of scenarios

you think thats safe?


For Each ws In Worksheets
With ws
If ws.Name Like "3888D" Then
With .Cells(Rows.Count, 1).End(xlUp)(2)
.Value = wsSource.Cells(2, 3).Value

.Offset(, 1).Resize(, 13).Formula = _
"=SUMPRODUCT((Detail!" & rData.Columns(1).Address & "=""" & ws.Name & """)*(Detail!" & rData.Columns(26).Address & "=B2)*(Detail!" & rData.Columns(24).Address & "))"
.Offset(, 1).Resize(, 13).Value = .Offset(, 1).Resize(, 13).Value
End With
End If

If ws.Name Like "#####" Then
With .Cells(Rows.Count, 1).End(xlUp)(2)
.Value = wsSource.Cells(2, 3).Value

.Offset(, 1).Resize(, 13).Formula = _
"=SUMPRODUCT((Detail!" & rData.Columns(1).Address & "=" & ws.Name & ")*(Detail!" & rData.Columns(26).Address & "=B2)*(Detail!" & rData.Columns(24).Address & "))"
.Offset(, 1).Resize(, 13).Value = .Offset(, 1).Resize(, 13).Value

End With
End If
End With
Next ws

[Bob Phillips]

Isn't 'Like "3888D"' the same as '= 38888D@?

Why bother testing if you do the same action regradless?