VBA in Excel cant execute this subroutine

[naikadit]

Hi I want to copy the data from one tab of the excel into another depending upon some conditions in the code but when I reun the subroutine it doesnt run I guess there is a mistake in the switch syntax

Sub Sort_tabs()
Dim fap As String, fo As String, one As String, two As String
Dim i As Integer, j As Integer, k As Integer, m As Integer, flag_core As Integer, flag_clear_cr As Integer
Dim flag_core_fap As Integer, flag_clear_cr_fap As Integer, flag_core_fo As Integer, flag_clear_cr_fo As Integer
Dim flag_core_one As Integer, flag_clear_cr_one As Integer, flag_core_two As Integer, flag_clear_cr_two As Integer
flag_core = 1
flag_clear_cr = 1
flag_core_fap = 1
flag_clear_cr_fap = 1
flag_core_fo = 1
flag_clear_cr_fo = 1
flag_core_one = 1
flag_clear_cr_one = 1
flag_core_two = 1
flag_clear_cr_two = 1
Dim customer_end As Long

fap = "Formal answer provided"
fo = "Follow up"
one = "1st line"
two = "2nd line"

Sheets("All").Activate

customer_end = find_low(2, 1) - cals a function

customer_end = customer_end - 1

For i = 1 To customer_end
Select Case Cells(2, 1) - This cell has string values

Case Is = "Core"
If Cells(2, i) = "Core" Then
If flag_clear_cr = 1 Then
For m = 2 To 10000
Cells(m, 53).Clear
Next m
flag_clear_cr = 0
End If
For j = 1 To 53
Sheet3.Cells(flag_core, j) = Sheet1.Cells(i, j).Value
Next j
flag_core = flag_core + 1


If Cells(i, 19) = fap Then
If flag_clear_cr_fap = 1 Then
For m = 2 To 10000
Cells(m, 53).Clear
Next m
flag_clear_cr_fap = 0
End If
For j = 1 To 53
Sheet4.Cells(flag_core_fap, j) = Sheet1.Cells(i, j).Value
Next j
flag_core_fap = flag_core_fap + 1
End If

If Cells(i, 19) = fo Then
If flag_clear_cr_fo = 1 Then
For m = 2 To 10000
Cells(m, 53).Clear
Next m
flag_clear_cr_fo = 0
End If
For j = 1 To 53
Sheet5.Cells(flag_core_fo, j) = Sheet1.Cells(i, j).Value
Next j
flag_core_fo = flag_core_fo + 1
End If
If flag_clear_cr_one = 1 Then
For m = 2 To 10000
Cells(m, 53).Clear
Next m
flag_clear_cr_one = 0
End If
For j = 1 To 53
Sheet6.Cells(flag_core_one, j) = Sheet1.Cells(i, j).Value
Next j
flag_core_one = flag_core_one + 1

If Cells(i, 15) = two Then
For m = 2 To 10000
Cells(m, 53).Clear
Next m
flag_clear_cr_two = 0
End If
For j = 1 To 53
Sheet7.Cells(flag_core_two, j) = Sheet1.Cells(i, j).Value
Next j
flag_core_two = flag_core_two + 1

End If
End Select

Next i

End Sub

 

[SkipVought]

Hi,

Put a break in the code and step to discover what's happening.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Anyway, to make your code more readable, indent it:

http://www.oaltd.co.uk/Indenter/Default.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

A question:

You have variable i, used as BOTH a ROW incriment and COLUMN incriment. Is that by design?

                If Cells(2, [b]i[/b]) = "Core" Then
                If Cells([b]i[/b], 19) = fap Then

Also, I'm guessing that your logic could be revisited and simplified.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Furthermore this value never changes...

        Select Case Cells([b]2, 1[/b])  '- This cell has string values

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[naikadit]

Hey Skip,

thanks for the reply.Actually I am trying to get data from the one tab (Sheet"All") and dividing this data into different sheets according to the string value in the 1st colummn 2nd row.I guess Evrything is working but one doubt how do i pass the string value in Sheet4.Cells(flag_core_fap, j) = Sheet1.Cells(i, j).Value

 

[SkipVought]

Have you tried using any of the native Excel reporting and analsis tools in the DATA menu, like PivotTable, Sort, Filter or Subtotal???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[naikadit]

no I dont want it in pivot table and I want it to be auto divided. So I guess I need to write the code but Skip everything is working fine now I just want to copy the string value of Sheet1.Cells(i, j) into Sheet5.Cells(flag_core_fo, j) ..please let me know

 

[SkipVought]

"...everything is working fine now..." "I just want to copy the string value of Sheet1.Cells(i, j) into Sheet5.Cells(flag_core_fo, j) "

Sheet5.Cells(flag_core_fo, j).value = Sheet1.Cells(i, j).value

I don't understand what you are asking???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[naikadit]

Hey skip Actually the calue in cell(i,2) is a 10 digit number with 4 zeros so when i cam copying the data only the 6 digits are copied the 4 zeros in the front are not...I want your help in this...

 

[naikadit]

I want that the cell which I am copying this data should have 10 digits like for eg 0000612128 instead of 612128

 

[SkipVought]

Please be clear. A number is a number regardless of leading zeros. You can FORMAT the cell to DISPLAY leading zeros, but that will not change the underlying VALUE in the cell. Also a number is a value that you can do math with.

However, if you these values are really INDENTIFIERS (like INVOICE, PART, EMPLOYEE) then they are not REALLY numbers; they are STRINGS of numeric characters, maybe. In these cases you can use the Format function to return a STRING padded with ZEROS...

Sheet5.Cells(flag_core_fo, j).value = Format(Sheet1.Cells(i, j).value,"0000000000")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Another way is to copy the format too:
Sheet5.Cells(flag_core_fo, j).Value = Sheet1.Cells(i, j).Value
Sheet5.Cells(flag_core_fo, j).NumberFormat = Sheet1.Cells(i, j).NumberFormat

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886