CODEWITHSUNDEEP
vbaexcel
AGB04
AGB04

Reputation: 3

Using VBA to reference a cell in a range

I'm writing an Excel macro using VBA and I have an input box to have the user select a "task". The entire row is then assigned variable "myCell". Then using "myCell", assign "SST" the value of cell "F" within that range. I'm new to coding so I'm a little stuck. Code so far is as follows:

Dim SST As Range
Dim myCell As Range
Dim Task As Range

Sheets("Sheets1").Activate
Set myCell = Application.InputBox(prompt:="Select a task", Type:=8)
Set Task = myCell.EntireRow

SST = Sheets("Sheet1").Range(Task).Cells(0, 6).Value

I have also tried it as:

SST = Range("F" & Task).Value

Neither seem to work. If you could help, that would be great!

Upvotes: 0

Views: 91

Answers (1)

SJR
SJR

Reputation: 23081

You have declared SST as a Range so the correct syntax would be (since Task is already defined as a range itself)

Set SST = Task.Cells(6)

If you want it to be assigned the value change the declaration to Variant (or whatever) and use

SST = Task.Cells(6).value

Upvotes: 1

Related Questions