PDA

View Full Version : referring to numbers/alphabets



Anomandaris
11-16-2009, 10:19 AM
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
11-16-2009, 10:22 AM
If Range("A1").Value Like "####?" Then

Anomandaris
11-16-2009, 10:36 AM
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
11-16-2009, 11:26 AM
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
11-16-2009, 11:51 AM
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
11-16-2009, 12:15 PM
That is because you are testing for a sheetname like ####?

Anomandaris
11-16-2009, 12:30 PM
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
11-17-2009, 03:36 AM
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
11-17-2009, 10:09 AM
Isn't 'Like "3888D"' the same as '= 38888D@?

Why bother testing if you do the same action regradless?