VBA Range Problem!

[sirantonycartwr]

Hi Guys,

Hope you are ok!

I'm getting this error: "Application-Defined or Object-Defined error".

On this line: "Set rng1 = .Range("A", CStr(i))".

I don't know what the hell it is talking about! It all looks fine to me and this is driving me mad!

The code should simply iterate through all worksheets which begin with ab or c. Then for each, it picks out the value of two cells, assigns them to variables. After this it puts the values into cells in Sheet1.

Here is the code:

Public Sub sbGetRecords()
On Error GoTo err

    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    
    Dim a As String
    Dim name As String
    Dim i As Integer
    
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.name, 1) = "A" Or Left(ws.name, 1) = "B" Or Left(ws.name, 1) = "C" Then
            i = i + 1
            a = ws.Range("M1").MergeArea.Cells(1, 1).Value
            name = ws.Range("C3").MergeArea.Cells(1, 1).Value
            
            With Sheets("Sheet1")
                Set rng1 = .Range("A", CStr(i))
                rng1.Value = "'" + a
                Set rng2 = .Range("B", CStr(i))
                rng2.Value = "'" + name
            End With
            
        End If
    Next
Exit Sub

err:
    MsgBox err.Description
    Resume Next
End Sub

Please help!

Antony

 

[arnelgp]

With Sheets("Sheet1")
Set rng1 = .Range("A" & i)
rng1.Value = "'" + a
Set rng2 = .Range("B" & i)
rng2.Value = "'" + name
End With

 

[sirantonycartwr]

Omg, something so stupid!

Thanks so much! I appreciate that! I'd spent about an hour continually going through the code trying to find out what I'd done.

I figured that since the argument was a string that it would naturally want a string passed to it!

 

[sirantonycartwr]

Ahhh, upon taking a second look, I see what I did wrong, for some reason I was passing it two arguments instead of concatenating the row and col into one argument!

Thanks again!

 

[Brianwarnock]

Missed this as it was not in the EXCEL forum.

Just

With Sheets("Sheet1") 
      .Range("A" & i) = "'" + a 
      .Range("B" & i) = "'" + name 
End With

is all that is required.
Sheets("Sheet1").Range is a range no need to set rng1 etc

Brian