Setting VBA Variables

Question

complete newbie here

I started a few VBA a few days ago, I have a simple question, but can't find what I'm doing wrong.

I am trying to make a button that will take the coordinates of the active cell and compare them with another sheet to get a specific value from another table. I set the variables for the column and row of the active cell, I want to do this to subsequently compare these locations with another sheet and get the value at the specified position on another sheet.

So far, I just wrote what I could find on the Internet, because I do not have formal training. Msgbox at the end is just to check if it is really typing a link.

Sub CommandButton1_Click()
Dim Arow As Range
Dim Acol As Range
Set Arow = Worksheets("Sheet1").Range(ActiveCell.Row)
Set Acol = Worksheets("Sheet1").Range(ActiveCell.Column)
MsgBox (Arow)
End Sub

So far I have a runtime error of the error "1004". A specific program or object error highlighting the fourth line. If someone can help me solve this problem or redirect me to some help, it will be very appreciated.

Answer 1

I think this will not work, you should put there

Set arow = Worksheets("Sheet1").Range(ActiveCell.Row & ":" & ActiveCell.Row)

Including there simply numbers will not work. For the column, you should put something like there C:C. For a column column see. This is qestion: Function to convert column number to letter?

Range . https://msdn.microsoft.com/en-us/library/office/ff836512.aspx.

, A1, "A1", "$ A $1", .., Range, Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1,1), Worksheets("Sheet1").Cells(2,2)), , , - .

Answer 2

ActiveCell.Row ActiveCell.Column , , .. B4, ActiveCell.Row 4, ActiveCell.Column 2. Range() , .. Range("C6") Range("G3:J8").

, Cells() , .. Range(Cells(2, 4), Cells(6, 8) , Range("D2:H6").

, , :

Sub CommandButton1_Click()
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Cells(ActiveCell.Row, ActiveCell.Column)
End Sub

Rng , ActiveCell, Sheet1. i.e Rng.Value = "Hello World", - Rng.PasteSpecial xlPasteAll ..

Answer 3

if you want to get the value from another sheet in the same place as activeCell, use this code,

Private Sub CommandButton1_Click()

    valueFromOtherSheet = Sheets("Sheet2").Range(ActiveCell.Address)
    MsgBox (valueFromOtherSheet)

End Sub

Answer 4

Like others, it just knows your variable types. This is another way to achieve the desired result.

Sub CommandButton1_Click()
    Dim Acell As Range
    Set Acell = Worksheets("Sheet2").Range(ActiveCell.Address)
    MsgBox "Value on ActiveSheet: " & ActiveCell.Value & vbNewLine & _
        "Value on Sheet2: " & Acell.Value
End Sub

Answer 5

Thank you all for your help and clarification. In the end, I was able to come up with some kind of code that seems to do what I need.

Private Sub CommandButton1_Click()
Dim cabDate As Range
Dim searchCol As Integer
Dim newindex As Range

Set cabDate = WorksheetFunction.Index(Range("A1:O9999"), ActiveCell.Row, 2)
searchCol = ActiveCell.Column
Set newindex = WorksheetFunction.Index(Worksheets("Deadlines").Range("A1:O9999"), cabDate.Row, searchCol)
MsgBox (newindex)
End Sub

I did not know about conflicting data types, so thank you all for your help.