Range question

Benic

New Member
Joined
Aug 2, 2013
Messages
35
Hello,

I have code which works:

Code:
Sub CopyVLOOKUP() '
' CopyVLOOKUP Macro
    
Range("C10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],brutto_cijene!R5C2:R65536C14,2,FALSE),"""")"
    Range("C10").Select
    Selection.AutoFill Destination:=Range("C10:C300")
    Range("C10:C300").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

I would like to manually change C10 and C300 values like C10 = Raspon1, C300 = Raspon2. I tried like this:

Code:
Sub CopyVLOOKUP() '
' CopyVLOOKUP Macro
'
    Dim Raspon1 As Integer
    Dim Raspon2 As Integer
    
    Raspon1 = 10
    Raspon2 = 300
    
    Range("Raspon1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],brutto_cijene!R5C2:R65536C14,2,FALSE),"""")"
    Range("Raspon1").Select
    Selection.AutoFill Destination:=Range("Raspon1:Raspon2")
    Range("Raspon1:Raspon2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

but I get error message.

Run time error "1004"
Method "Range" of object _Global failed

thanks in advance,
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Since Raspon1 is an integer, this makes no sense:
Range("Raspon1").Select
Also note that since Raspon1 is a variable and not a literal text value, it should not be placed in quotes.

If you want it to go to cell C10 still using this methodology, try:
Range("C" & Raspon1).Select
or
Cells(Raspon1,"C").Select
 
Upvote 0
I tried to replace

Selection.AutoFill Destination:=Range("C10:C300")
Range("C10:C300").Select
with
Selection.AutoFill Destination:= Range ("C" & Raspon1,"C" & Raspon2")
Range(("C" & Raspon1):("C" & Raspon2")).select

but I get Syntax error :(
 
Upvote 0
Why do you have a double-quote at the end of Raspon2?

Try changing your range reference in each case to:
Range("C" & Raspon1 & ":C" & Raspon2)
 
Upvote 0
It works, thanks.Is it possible to use this code with other excel files without copy paste module?Is is possible to change Raspon1, Raspon2, C columne for example to D but without entering into code, can we change it in area of Sheet1 or somewhere in function tabs?I have added shortcut of this macro to Quick Access Toolbar, can we add labels or something where we can change values of Raspon1, Raspon2, letter of columnes?thanks
 
Upvote 0
Yes, you can set up variables to have it run on any column you want. Just replace the hard-coded column reference with the variable.
Take a look at user InputBox, where when the code runs, you can have it ask the user which column they would like to run it on.
See: InputBox: Collect User Data/Input via Excel InputBox in VBA
 
Upvote 0
Thanks Joe4,

This is code for defining start and stop cell. The problem is that I can not use values defined in InputBox (Raspon1 and Raspon2) inside Sub CopyVLOOKUP(). What am I doing wrong?

Code:
Private Sub Workbook()


    Private Raspon1 As Long
    Private Raspon2 As Long


    On Error Resume Next


        Application.DisplayAlerts = False


            Raspon1 = Application.InputBox _
             (Prompt:="Molimo unesite broj pocetne celije.", _
                    Title:="Number of start cell", Type:=1)
                    
            Raspon2 = Application.InputBox _
             (Prompt:="Molimo unesite broj zavrsne celije.", _
                    Title:="Number of final cel", Type:=1)


    On Error GoTo 0


    Application.DisplayAlerts = True


        If Raspon1 = 0 Or Raspon2 = 0 Then


            Exit Sub


        Else
       
       [COLOR=#008000]What should be here??[/COLOR]
            
        End If


End Sub


Sub CopyVLOOKUP()


   
   
     'Raspon1 = I want to use number from InputBox
     'Raspon2 = I want to use number from InputBox
        
    Range("C" & Raspon1).Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],brutto_cijene!R5C2:R65536C14,2,FALSE),"""")"
    Range("C" & Raspon1).Select
    Selection.AutoFill Destination:=Range("c" & Raspon1, "c" & Raspon2)
    Range("c" & Raspon1, "c" & Raspon2).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Range("D" & Raspon1).Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-2],brutto_cijene!R5C2:R65536C14,13,FALSE),"""")"
    Range("D" & Raspon1).Select
    Selection.AutoFill Destination:=Range("D" & Raspon1, "D" & Raspon2)
    Range("D" & Raspon1, "D" & Raspon2).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
       
End Sub

thanks
 
Upvote 0
I did not declare variables on good way, I repair it.
Now, I have problem with Kolona1 and Kolona2 (Kolona1 is start cell ("C") and Kolona2 i final cell ("D"):



Public Kolona1 As String
Public Kolona2 As String

Public Raspon1 As Long
Public Raspon2 As Long

Private Sub Workbook()


On Error Resume Next


Application.DisplayAlerts = False


Raspon1 = Application.InputBox _
(Prompt:="Molimo unesite broj pocetne celije.", _
Title:="Number of start cell", Type:=1)

Raspon2 = Application.InputBox _
(Prompt:="Molimo unesite broj zavrsne celije.", _
Title:="Number of final cell", Type:=1)

Kolona1 = Application.InputBox _
(Prompt:="Molimo unesite slovo pocetne kolone.", _
Title:="Kolona_1", Type:=2)

Kolona2 = Application.InputBox _
(Prompt:="Molimo unesite broj zavrsne kolone.", _
Title:="Kolona_2", Type:=2)




On Error GoTo 0



End Sub


Sub CopyVLOOKUP()



Range(Kolona1 & Raspon1).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],brutto_cijene!R5C2:R65536C14,2,FALSE),"""")"
Range(Kolona1 & Raspon1).Select
Selection.AutoFill Destination:=Range(Kolona1 & Raspon1, Kolona1 & Raspon2)
Range(Kolona1 & Raspon1, Kolona1 & Raspon2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Range("D" & Raspon1).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],brutto_cijene!R5C2:R65536C14,13,FALSE),"""")"
Range("D" & Raspon1).Select
Selection.AutoFill Destination:=Range("D" & Raspon1, "D" & Raspon2)
Range("D" & Raspon1, "D" & Raspon2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False



End Sub
 
Upvote 0
So what exactly is the problem?

What exactly are you trying to do?
Are you asking the user to input the columns and rows separately, one-by-one?
Why not have them put in the whole range at once, or better yet, have them select it first, and just run the code against the selected range?
 
Upvote 0
Hello,

Are you asking the user to input the columns and rows separately, one-by-one?
Yes, that is wright. In Private Sub Workbook() I want to define rows and columns but I have problem with defining colums (C and D). How I can define C column as variable?
I have defined Kolona1 and Kolona2 (C and D column) like string, and put Type2 in:

Kolona1 = Application.InputBox _
(Prompt:="Molimo unesite slovo pocetne kolone.", _
Title:="Kolona_1", Type:=2)

Kolona2 = Application.InputBox _
(Prompt:="Molimo unesite broj zavrsne kolone.", _
Title:="Kolona_2", Type:=2)

but in
Sub CopyVLOOKUP() it does not recognise these 2 variables?

thanks :)

 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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