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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Is that not right? I though that the search got passed to rng and then passed to c?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top