Use specific cell instead of ActiveCell

[sparky2205]

Hi folks,
I'm trying to create a macro that will allow the user to input newrows in a protected worksheet based on a starting point and a number ofrows.
The newly entered rows will be copies of the row above where the new rows willbe entered. That row contains formulas which must copy with the rows.
I can get this working using ActiveCell but I don’t want the user to have to selectthe row in the worksheet before running the macro.
So what I want is to allow the user toenter the starting position and the number of rows and based on this informationperform the task.

This is where I’m at but I’m having aproblem assigning the starting position to a variable.

Sub InsertMultipleRows()
Dim s As Variant
Dim n As Variant
Dim r As Range
'Get the starting position
s= Application.InputBox("Enter the starting position", , , , , , , 1 +2)
If s = False Then
Exit Sub
ElseIf s = "" Then
Exit Sub
End If
r= "A" & s
'Get the number of rows to input
n= Application.InputBox("Enter the number of rows to input", , , , , ,, 1 + 2)
If n = False Then
Exit Sub
ElseIf n = "" Then
Exit Sub
End If
'Unprotect the sheet
ActiveSheet.Unprotect Password:=""
'Copy the cell
r.EntireRow.Copy
'Fill down the required numbe of times
Range(r.Offset(1, 0), r.Offset(n, 0)).EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

I think the problem is with r = "A" & s
I’m getting the “Object variable or Withblock variable not set” error.

Any help would be greatly appreciated.

 

[sparky2205]

Thanks again for your help Fluff.
I like to understand the answers I get not just use them.
It's always a good day when you learn something new.
Have a good weekend.

 

[Fluff]

Glad to help & thanks for the feedback

 

[sparky2205]

Hi, I'm back again on this same issue.
I used the code as previously sorted out for me by Fluff. Here is the code:

Sub InsertMultipleRows()
    Dim s As Variant
    Dim n As Variant
    Dim r As Range
    ' Get the starting position
    s = Application.InputBox("Enter the starting position", , , , , , , 1 + 2)
    If s = False Then
        Exit Sub
        ElseIf s = "" Then
        Exit Sub
        End If
    Set r = Range("A" & s)
    ' Get the number of rows to input
    n = Application.InputBox("Enter the number of rows to input", , , , , , , 1 + 2)
    If n = False Then
        Exit Sub
        ElseIf n = "" Then
        Exit Sub
    End If
    ' Unprotect the sheet
    ActiveSheet.Unprotect Password:=""
    ' Copy the row
    r.EntireRow.Copy
    ' Fill down the required numbe of times
    Range(r.Offset(1, 0), r.Offset(n, 0)).EntireRow.Insert Shift:=xlDown
    ' Clear the contents of the copied rows except for formulas
    Rows(s).Resize(n).SpecialCells(xlConstants).ClearContents
    ' Don't copy to clipboard
    Application.CutCopyMode = False
    ' Reprotect the sheet
    ActiveSheet.Protect Password:=""
End Sub

This worked fine all through my testing. However, when I try to execute it now it gives me our friend, the 400 error after I enter the number of rows to add. When I step through the code it appears that the variable r is not being set. The code bombs out at...
Rows(s).Resize.SpecialCells(xlConstants).ClearContents
...with the Run-time error '1004': Application-denfined or object-defined error.

Any help is appreciated.

 

[sparky2205]

Some more information on this.
The above code will work perfectly fine as long as the starting position selected by the user isn't a blank cell.
Can anyone explain why this would be a problem? If so any ideas how I could change the code to allow the starting position to be a blank cell?

 

[sparky2205]

Hi guys,
I'm still trying to figure this out.
If I understand it correctly the Range object returns the Value of a range, not the address. At least that's what's happening with my code when I step through it. So "Set r = Range("A" & s)" is assigning the value of the cell to r not the address.
But if that's the case then how does the rest of the code work at all?
"r.EntireRow.Copy" wouldn't even be looking at a cell address. So how does it know which row to copy?
As I said previously this code does work as long as the starting point is not a blank row.
The fact it works when the starting point is not blank makes more sense when relating it to the Range object returning a value rather than an address.
However, I still don't understand how the code is referencing the correct range based on its value.

Any help would be greatly appreciated.

 

[Fluff]

I'm not quite sure what is going wrong, or why, as it works fine for me if the start point is blank or not.
Do you have any merged cells?

 

[sparky2205]

Row 1 has merged cells. This is where I place my macro buttons and the Title. This row will never be referenced in the macro.
I still don't understand how the code uses the value of the Range rather than the address to reference the cell.

 

[Fluff]

It's not using the value, when you declare a variable as range it uses the range.
If you type ?r.address into the immediate window it will show you the address of the range

 

[sparky2205]

Thanks for clearing that up. It was confusing me.
But there is a reason I suggested it was using the value.
When I add a breakpoint to my code and step through it I can see the value of the variables by hovering over them.
After the line "Set r = Range("A" & s)" executes, if I hover over the variable r it shows me r has been assigned the value of the cell not the address.
Also when I type ?r.address into the immediate window and press Enter I get Run-time error'424': Object required
It looks like there is something strange with the behaviour of the r variable but I cannot figure it out.

Thanks for sticking with me on this one Fluff.

 

[Fluff]

If you add this line

Set r = Range("A" & s)
[COLOR=#0000ff]Debug.Print r.Address
[/COLOR]

It will print the address to the immediate window