VBA problem: searching across for "1" and assignin

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
I have the following lines that don't work :)

Code:
        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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Is that not right? I though that the search got passed to rng and then passed to c?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
dbmathis said:
Is that not right? I though that the search got passed to rng and then passed to c?

You need to assign an object to rng:

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

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

Ok, cool that stopped the error.

If there is a 1 in cell I52 should not the below code start in cell C52, look to the right and find the 1 in I52 and then paste a comment in I60?

Code:
    With Sheets("Cash Flow Statement")
        If CheckBox1 = True Then
        Dim Tmp As String
        Dim c As Range
        Dim rng As Range
        Set rng = Rows(52)
        Set c = rng.Find(1, After:=Range("C52"), LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
            On Error Resume Next
                Tmp = Sheets("Cash Flow Statement").c.Offset(5, 0).Comment.Text
                If Err.Number = 0 Then
                    .c.Offset(8, 0).Comment.Text Text:=Sheets("Cash Flow Statement").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 With
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This worked for me:

Code:
Private Sub CommandButton1_Click()
   Dim Tmp As String
   Dim c As Range
   Dim rng As Range
   With Sheets("Cash Flow Statement")
      If CheckBox1 = True Then
         Set rng = Rows(52)
         Set c = rng.Find("1", After:=.Range("C52"), LookIn:=xlFormulas, 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
End Sub
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

The comment is still not being created in cell I60 when I run the code?

Here is what that range in that row looks like.
Transaction Register Starting Dec-16-2005 Draft.xls
CDEFGHIJKLMNOPQR
52  1         
Cash Flow Statement


Does the fact that a formula is creating the "1" effect the performance of this code?
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Ok, the code is running on the wrong sheet.

It's running on the sheet that the userform is in. I thought that the With statment fixed that. Anyone see how to correct this in the above code?
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Thanks Andrew,

This is what I have:

Code:
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:=xlFormulas, 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

For some reason this is still not working. I have two sheets as you can see, Transaction Register, and Cash Flow Statement. You can also see the row of cells I am working with above in the HTML Maker example. I do appeciate all your help Andrew. Do you see anything that might be wrong here?

I have been starring at the code for half an hour now. :)

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,767
Members
412,291
Latest member
marypolitan
Top