4

complete novice here

I started some VBA a few days ago, I have simple question but cant seem to find what I am doing wrong.

I am trying to make a button which will take the coordinates of the active cell and compare them to another worksheet to retrieve a specific value from another table. I set variables to the active cell column and row, I want to do this so I can later compare these locations to another worksheet and get the value at a specified position on another worksheet.

So far I have written simply what I could find on the internet as I have no formal training. The msgbox at the end is just to test whether or not it actually picks up the reference.

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 error run-time error '1004' Application defined or object defined error highlighting the 4th Row. If anyone could help me solve this or redirect me to some help it would be much appreciated.

Dingge
  • 51
  • 4

5 Answers5

1

I think this won't work, you should put there

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

Putting there simply number won't work. For the column, you should put there somethong like C:C. For getting letter of column, see this qestion: Function to convert column number to letter?

For more information about Range property, please see official documentation https://msdn.microsoft.com/en-us/library/office/ff836512.aspx.

The thing is, that you have to supply either the address in so called A1 reference, which is "A1", or "$A$1" or name of cell, etc, or you have to supply two Range objects, such as two cells Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1,1), Worksheets("Sheet1").Cells(2,2)), which defines area starting with upper-left corner in first parameter and lower right in second parameter.

Community
  • 1
  • 1
holmicz
  • 577
  • 4
  • 15
1

ActiveCell.Row and ActiveCell.Column returns you some Integer value representing number of row and column, i.e. if you point cell B4, ActiveCell.Row would return 4, and ActiveCell.Column gonna return 2. An Range() property need as an argument whole adress for some range, i.e. Range("C6") or Range("G3:J8").

When you have your column as a number, you can use Cells() property for pointing first and last cell in your range, i.e. Range(Cells(2, 4), Cells(6, 8) would be the same range as Range("D2:H6").

Following this, one of the ways that you can do what you have described is:

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

Now you have under variable Rng an Range of the same coordinates as ActiveCell, but in Sheet1. You can pass some value into i.e Rng.Value = "Hello World", paste something with Rng.PasteSpecial xlPasteAll etc.

Limak
  • 1,511
  • 3
  • 12
  • 22
0

if you want the value from other sheet at the same location as activeCell, use this code,

Private Sub CommandButton1_Click()

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

End Sub
nightcrawler23
  • 2,056
  • 1
  • 14
  • 22
0

Like the others have said, it's just about knowing your variable types. This is another way you could achieve what you want

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
CallumDA
  • 12,025
  • 6
  • 30
  • 52
0

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

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 wasn't aware about conflicting data types so thank you all for the assistance.

Dingge
  • 51
  • 4