VBA error on range :

[jbesclapez]

Hi guys,

I get a vba error and I do not understand why. It says error runtime 91.
It seems to be with the line
InputRngAddress = "Source!$A$1:$A$10"
I hardcoded it but it should be a range. I added the worksheet name too.

Thanks for your help

   Dim InputRngAddress As Range
    Dim ReplaceRngAddress As Range
    Dim LastRowSourceA As Long
    Dim LastRowDestinationA As Long
    Dim wss As Worksheet
    Dim wsd As Worksheet
    xTitleId = "SonumByNic"
    Dim Rng As Range

    'Source XML
    Set wss = ActiveWorkbook.Sheets("Source") 'set to current worksheet name
    LastRowSourceA = wss.Cells(Rows.Count, 1).End(xlUp).Row
   ' MsgBox "Entered value is " & LastRowSourceA
   ' InputRngAddress = "Source!$A$1:$A$" & LastRowSourceA
    InputRngAddress = "Source!$A$1:$A$10"
   
    'MsgBox "Entered value is " & InputRngAddress
    'Destination XML
    Set wsd = ActiveWorkbook.Sheets("Replace") 'set to current worksheet name
    LastRowDestinationA = wsd.Cells(Rows.Count, 1).End(xlUp).Row
    ReplaceRngAddress = "Replace!$A$1:$B$" & LastRowDestinationA
   
   ' MsgBox "Entered value is " & ReplaceRngAddress
   
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRngAddress.Columns(1).Cells
        InputRngAddress.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
    Next
    Application.ScreenUpdating = True

 

[RoryA]

That is a String, not a Range object. You need:

Set Inputrngaddress = Sheets("Source").range("A1:A10")

though that makes your variable name extremely confusing!

 

[Joe4]

InputRngAddress = "Source!$A$1:$A$10"

Anything enclosed in double-quotes is treated as literal text.
You need to use Range... when setting ranges, and neeed to use the Set command.
You also need to use the Sheet reference, i.e.

Set InputRngAddress = Sheets("Source").Range("A1:A10")

 

[jbesclapez]

Thanks for the solution. And also for the explanation Have a great day guys!

 

[Joe4]

You are welcome.
Glad we were able to help!