VBA problem: searching across for "1" and assignin

[dbmathis]

I have the following lines that don't work

        Dim c As Range
        Dim rng As Range
        Set c = rng.Find(1, After:=Range("C52"), LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

What this is supposed to do is start in cell C52 of sheet "Cash Flow Statements" and search across the sheet in a right direction until the number 1 is found and assign the cell location that the 1 is in to variable c.

The code above just does not work.

Thanks.

 

[Andrew Poulsom]

What's the number format of the entry in I52?

 

[dbmathis]

I52 contains this formula

=IF(MONTH(I54)=MONTH(TODAY()),1,"")

The format is General

I tried the VBA with that formula and without the formula with just the number 1 in the cell. Neither want to work.

 

[Andrew Poulsom]

Try:

Private Sub CommandButton1_Click()
    Dim Tmp As String
    Dim c As Range
    Dim rng As Range
    With Sheets("Transaction Register")
        If CheckBox1 = True Then
                .[M41].End(3)(2, 1).Value = TextBox5.Text
                .[K41].End(3)(2, 1).Value = TextBox3.Text
                .Range("C212").End(xlUp).Offset(2, -1).Value = TextBox2.Value
                .Range("C212").End(xlUp).Offset(2, 1).Value = TextBox3.Value
                .Range("C212").End(xlUp).Offset(2, 2).Value = TextBox5.Value
                .Range("C212").End(xlUp).Offset(3, 1).Value = TextBox6.Value
                .Range("C212").End(xlUp).Offset(2, 0).Value = TextBox1.Value
        End If
    End With
    With Sheets("Cash Flow Statement")
        If CheckBox1 = True Then
            Set rng = .Rows(52)
            Set c = rng.Find("1", After:=.Range("C52"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
            If Not c Is Nothing Then
                On Error Resume Next
                Tmp = c.Offset(8, 0).Comment.Text
                If Err.Number = 0 Then
                    c.Offset(8, 0).Comment.Text Text:=c.Offset(8, 0).Comment.Text & _
                    Chr(10) & TextBox1.Text & " " & TextBox5.Text
                Else
                    c.Offset(8, 0).AddComment
                    c.Offset(8, 0).Comment.Text Text:=TextBox1.Value & " " & TextBox5.Text
                End If
                On Error GoTo 0
            End If
        End If
    End With
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox5.Text = ""
    TextBox6.Text = ""
End Sub

I changed it to look in xlValues. I also removed the dots before c - the object's Parent is already that sheet.

 

[dbmathis]

Thank you VERY much Andrew! I have been trying to work on this sheet between calls and you have been a FANTASTIC help.