Next Without For (and other issues)

[KateA]

Hi,

I’ve currently making a workbook in which I have about eighty sheets, each relating to a specific customer, and two or three other sheets summary sheets which analyse the whole portfolio.
In one of these summary sheets I want to pose a few (kind of) questions that look like
1. The percentage of income generated by ________ that comes from _______ fees is _____%
2. …
3. A list of companies who’s exposure from ______(K5)__ is greater than___(M5)_____;
…….and that sort of thing, where into the cells that look like_____ I can type different criteria and so return different answers in the answer space.
Each worksheet is named after its company. The layout of all worksheets is the exact same. In the column D is the names of the sources of income and in column H is the amount of income generated by each source. Some, in fact most, cells in the H column are zero.
I want to write a macro that takes the words that I’ve typed into the cells K5 and M5 and searches each sheet in turn for these words, selects the number parallel to each word, compares the two numbers, and if figure A is bigger than B returns the name of the current worksheet into the column O in the summary worksheet where I want my list.

Could anyone tell me why the error message Next without For keeps appearing? I've checked my indenting very carefully and its all in line. Aswell as this, im painfully aware that there are many other aspects of my macro-writing that could be improved upon, apart from just my propensity to get "errored". If anyone has an actually functional/more elegant solution to my problem I'd welcome it gladly.

Sub Compare()
Dim ThingA As String, ThingB As String
Dim NumberA As Integer, NumberB As Integer
  A = Worksheets("PieChart").Range("K5").Value
  B = Worksheets("PieChart").Range("M5").Value

Dim ColumnO As Range
  ColumnO = Worksheets("PieChart").Column(15)
  ColumnO.ClearContents

  For Each Worksheet In Worksheets
 
  Dim SearchHere As Range
  Set SearchHere = Range("D52: D61")
  For Each MyCell In SearchHere
  If MyCell.Value = ThingA Then
  MyCell.Offset(0, 4).Value = NumberA
  End If
  Next MyCell
 
  For Each MyCell In SearchHere
  If MyCell.Value = ThingB Then
  MyCell.Offset(0, 4).Value = NumberB
  End If
  Next MyCell
 
  Dim SheetName As String
  Dim CellIWant As Range
  CellIWant = Worksheets("PieChart").Column(15).End(xlUp).Offset(1, 0)
  SheetName = ActiveSheet.Name

  If NumberA > NumberB Then
  CellIWant.Value = SheetName
  Else
  If NumberA < NumberB Then
  CellIWant.Value = Nothing
  End If
 
  Next Worksheet
 
End Sub

 

[Luke M]

The last block is your problem

If NumberA > NumberB Then
  CellIWant.Value = SheetName
Else 'Here's the middle part of an If statement
If NumberA < NumberB Then 'New If statement
  CellIWant.Value = Nothing
End If 'End of new If
'...
'NEVER closed first If

I believe it should be rewritten to:

If NumberA > NumberB Then
  CellIWant.Value = SheetName
ElseIf NumberA < NumberB Then
  CellIWant.Value = Nothing
End If

BTW, what happens if NumberA = NumberB?

 

[KateA]

Thank you! Unfortunately I'm now experiencing an "object doesn't support this property or method" error. Hahaha I thought it was very unlikely that they would be equal so i didn't bother writing anything in...I probably should shouldn't I? I'll just add

ElseIf NumberA = NumberB Then
CellIWant.Value = Nothing


I also just discovered that I named something "ThingA/ThingB" in one part of the code and "A/B" in another so i fixed that but im still getting the same error message....

Thanks for your time!

 

[NARAYANK991]

Hi Kate ,

I am not sure that the following code will do what you want , but I think the errors should be taken care of.

Sub Compare()
    Dim ThingA As String, ThingB As String, SheetName As String
    Dim NumberA As Integer, NumberB As Integer
    Dim SearchHere As Range, ColumnO As Range, CellIWant As Range

    NumberA = Worksheets("PieChart").Range("K5").Value
    NumberB = Worksheets("PieChart").Range("M5").Value

    Set ColumnO = Worksheets("PieChart").Columns(15)
    ColumnO.ClearContents

    For Each Worksheet In Worksheets
        Set SearchHere = Range("D52:D61")
        For Each MyCell In SearchHere
            If MyCell.Value = ThingA Then
              MyCell.Offset(0, 4).Value = NumberA
            End If
        Next MyCell
        For Each MyCell In SearchHere
            If MyCell.Value = ThingB Then
              MyCell.Offset(0, 4).Value = NumberB
            End If
        Next MyCell
        Set CellIWant = Worksheets("PieChart").Columns(15).End(xlUp).Offset(1, 0)
        SheetName = ActiveSheet.Name

        If NumberA > NumberB Then
          CellIWant.Value = SheetName
        Else
          If NumberA < NumberB Then
              CellIWant.Value = vbNullString
          End If
        End If
    Next Worksheet
End Sub

A few points about coding :

1. Try to declare all your variables together at the beginning ; spreading the declarations all over the code is not good practice.

2. When assigning a range , the SET keyword should be used.

3. When assigning a value to a range / cell , use the .VALUE property ; this is good practice.

4. Assign the right kind of default values depending on the type of variable to which you are assigning the default value ; thus assign the 0 ( zero ) value to INTEGER / LONG / SINGLE / DOUBLE type variables , assign the VBNULLSTRING value to a STRING type variable ,... Assign NOTHING only to objects , such as ranges , worksheets ,...

5. Make it a habit to use Option Explicit at the beginning ; you have declared NumberA and NumberB , but you are assigning values to A and B.

Narayan

 

[KateA]

Thank you very much!