select case help

[mercmannick]

Sub Define_Area()
Dim Ws, WsS70 As Worksheet, rngLook As Range
Dim LR, LC As Long
Dim intTotal As Integer
Dim Istr As String
    LR = Worksheets("Master Sheet").Range("B65536").End(xlUp).Row 'define lastrow of data
    LC = Worksheets("Master Sheet").Range("B4" & LR).End(xlToRight).Column ' define last column of data


        
    Set Ws = Worksheets("Master Sheet")
     Set WsS70 = Worksheets("Pivot S70")
     
     
    For intTotal = 1 To LR
     Select Case Ws.Range("P" & intTotal).Value
    
        
        Case "S03B"
        If Left(Istr, 4) = "S03B" Then
            WsS70.Range("O1").Value = "S03B/C"
            WsS70.Range("P1").Value = WorksheetFunction.CountIf(Ws.Range("P5:P" & LR), "S03B/C")
        Case "S03E"
        If Left(Istr, 4) = "S03E" Then
            WsS70.Range("O2").Value = "S03E"
            WsS70.Range("P2").Value = WorksheetFunction.CountIf(Ws.Range("P5:P" & LR), "S03E")
        Case "S03F"
        If Left(Istr, 4) = "S03F" Then
            WsS70.Range("O3").Value = "S03F"
            WsS70.Range("P3").Value = WorksheetFunction.CountIf(Ws.Range("P5:P" & LR), "S03F")
        Case "S03G"
        If Left(Istr, 4) = "S03G" Then
            WsS70.Range("O4").Value = "S03G"
            WsS70.Range("P4").Value = WorksheetFunction.CountIf(Ws.Range("P5:P" & LR), "S03G")
        End Select
    Next intTotal

Can anyone show me where i am going wrong on this

trying to look in col p, if first 4 letters = any of the above select case
then count how many and put the value in WsS70

Hope this makes sense

Merc

 

[tusharm]

What does "where i am going wrong on this" mean? What do you expect to happen? What is actually happening?

If you want to check the first 4 letters of P{x}, what do you think you should be checking in the Select Case statement?

What is the role if Istr? Why do you check it right after each Case xxx clause?

 

[Cindy Ellis]

Hi mercmannick,
Making some pretty huge guesses about what you're trying to do, I modified your code as follows:

Sub Define_Area()
Dim Ws, WsS70 As Worksheet, rngLook As Range
Dim LR, LC As Long
Dim intTotal As Integer
Dim Istr As String
Dim TestCell As Range
    LR = Worksheets("Master Sheet").Range("B65536").End(xlUp).Row 'define lastrow of data
    LC = Worksheets("Master Sheet").Range("B4" & LR).End(xlToRight).Column ' define last column of data


        
    Set Ws = Worksheets("Master Sheet")
     Set WsS70 = Worksheets("Pivot S70")
      
    For Each TestCell In Ws.Range("P1:P" & LR)
        Istr = Left(TestCell.Value, 4)
     Select Case Istr
        Case "S03B"
            WsS70.Range("O1").Value = "S03B/C"
            If TestCell.Value = "S03B/C" Then 
                WsS70.Range("P1").Value = WsS70.Range("P1").Value + 1
            End If
        Case "S03E"
            WsS70.Range("O2").Value = "S03E"
            If TestCell.Value = "S03E" Then
                WsS70.Range("P2").Value = WsS70.Range("P2").Value + 1
            End If
        Case "S03F"
            WsS70.Range("O3").Value = "S03F"
            If TestCell.Value = "S03F" Then
                WsS70.Range("P3").Value = WsS70.Range("P3").Value + 1
            End If
        Case "S03G"
            WsS70.Range("O4").Value = "S03G"
            If TestCell.Value = "S03G" Then
                WsS70.Range("P4").Value = WsS70.Range("P4").Value + 1
            End If
        End Select
    Next TestCell
End Sub

Some of the code may still be extraneous, and from the name of the 2nd sheet, I'm wondering if a PivotTable might be more suited to your needs. It would really be helpful if you could post some data, and the output you expect with it.
Regards,
Cindy

 

[mercmannick]

Cindy Ellis

hi thank you for code however it is putting just the values of Istr in "o1" etc
in "P1" it should be counting how many times the Istr value appears,

as to pivot question , the strings are example

S08 A BP
S08 A HT
S08 A HT ASEA
S08 A HT ASEA BP
S08 A HT BP
S08 NONE MRP

thts why pivot wont work

if on this example istr is S08 then i would need count of all the times all the above are on master sheet


Thanks

Merc

 

[Cindy Ellis]

Hi mercmannick,
The code I attached adds 1 each time the selected value is found, so it should (and did when I tested it), add up the total and put it in P1-P4, depending on the labeled value.
One difference between the data and the macro, though, is that in your most recent example, it appears that there is a space between the S08 and the next character. This would fail the case statement in every case, so nothing would be totaled.
I think I would still consider a pivot table, but not of the "original" data. Can you use a "helper" column? Take a look at the following exampe...is this what you need?

Book1
	A	B	C	D	E
1	Original	New		CountofNew
2	S08ABP	S08A		New	Total
3	S08BHT	S08B		S08A	4
4	S08AHTASEA	S08A		S08B	1
5	S08AHTASEABP	S08A		GrandTotal	5
6	S08AHTBP	S08A
7	S08NONEMRP
Sheet1

I changed one of your original values from "A" to "B" to show that it would total.

Cindy

 

[mercmannick]

For Each TestCell In Ws.Range("P4:P" & LR)
        Istr = Left(TestCell.Value, 4)
     Select Case Istr
     Case "S70A"
            WsS70.Range("O7").Value = "S70"
            If Left(TestCell.Value, 4) = "S70A" Then
                WsS70.Range("P7").Value = WsS70.Range("P7").Value + 1
                End If

Sorted out like this thanks Cindy.

Zflex S70.xls
	A	B	C	D	E	F
74
75
76	original				required
77	Short MRP	Short MRP			Short MRP	Short MRP
78	P02	S08 A HT			S08	S08 A HT
79	Z04	C&T HYDE			SPS	C&T HYDE
80	29L	MATTHEW COMLEY SUP			SPS	MATTHEW COMLEY SUP
81	04H	SAM RICHARDS			MAG	SAM RICHARDS
82	4NZ	NOT IN USE			NMB	NOT IN USE
83	I60	S03W M60 AEROSTAR			S03W	S03W M60 AEROSTAR
84	I05	S03C L21 + L26			SO3B/C	S03C L21 + L26
85	I05	S03C L21 + L26			SO3B/C	S03C L21 + L26
86	I06	S03B/C C CLASS			SO3B/C	S03B/C C CLASS
87	I06	S03B/C C CLASS			SO3B/C	S03B/C C CLASS
88	01Y	MISC PROC NEW PART			SO3E	MISC PROC NEW PART
89	I41	S03E M88			SO3E	S03E M88
90	I46	CELL E RED'NT MATL			SO3E	CELL E RED'NT MATL
91	I46	CELL E RED'NT MATL			SO3E	CELL E RED'NT MATL
92	P19	S08 NONE MRP			S08	S08 NONE MRP
DATA

How can i write in VBA to make col E output desired results above based on the value in Col F (similar to Select Case)

Many Thanks

Merc

 

[mercmannick]

Select Case Istr
     Case "S70A"
           'Ws.Range(TestCell).Value = "S70"
            If Left(TestCell.Value, 4) = "S70A" Then
                Ws.Range.Offset(0, -1).Value = "S70"
                End If

am i going down right road to try and do something as above for this ?

Thanks

Merc

 

[mercmannick]

solved

thanks all

Case "S70A"
            If Left(TestCell.Value, 4) = "S70A" Then
            TestCell.Offset(0, -1).Value = "S70"
                End If

Merc