Object required?!?!

[wsk92]

Excel keeps saying there is an error with

startd = wb1.Sheets("Master List").Range(i, 10)

anyway i change it (taking away set, changing the dim of startd, doesnt seem to work)

Please help. (i am aware that there is probably a few other issues with this that i havent come across.....macros are hard!)

Sub COLLECT_INFO()

Dim i As Integer


Dim Weld As Variant, Elect As Variant, Paint As Variant, Assem As Variant
Dim wb1 As Workbook


Set wb1 = Workbooks.Open("EV ARC Master List (version 1).xlsx")


Dim SD As String
Dim FD As String


SD = "Project Start"
FD = "Project Finish"


i = 4


Dim startd As Integer
startd = wb1.Sheets("Master List").Range(i, 10)
Dim ARC_NUM As Integer
ARC_NUM = wb1.Sheets("Master List").Range(i, 2)


Weld = DateAdd(w, Sheets("Projects").Range(19, 4), start_d)
Elect = DateAdd(w, Sheets("projects").Range(20, 4), start_d)
Paint = DateAdd(w, Sheets("projects").Range(21, 4), Weld)
Assem = DateAdd(w, Sheets("projects").Range(22, 4), Paint)

thanks for the help.

Wsk92

 

[Michael M]

Change the range reference to cell reference

wb1.Sheets("Master List").Range(i, 10)

TO

wb1.Sheets("Master List").Cells(i, 10)

 

[wsk92]

thats worked, thanks, but now its saying application-defined or object-error. ive change startd as string, and as a date and both times it doesnt work. can you help me with that at all?

 

[Michael M]

Have you change ALL of the Range references to Cells references ??
Is that ALL of the code ??

 

[wsk92]

I have sorted it out now, thanks for the reply! I do have another issue that is in the line below its asking for a object or with block variable, but this is a function surely:

Application.Goto reference:=Cells(fndrow.Row, fndcol.Column).Activate

fndrow refers to a object to find a specific row, and fndcol finds a specific column (still having trouble with fndrow as its a date though)

thanks again for the help.

William

 

[Michael M]

Have you defined the variables fndrow and fndcol ??
AND
as mentioned it's difficult to advise without seeing ALL the code

 

[wsk92]

thanks again for the reply. sorry im being so vague. the full code is attached below.

Im basically at the first stage of creating a loop, but began by making it simpler, without the i = etc.

the final part is where im trying to find the intersection between two variables! I tried using match function but had more difficulties so went back to this type.

thanks,

W

Sub COLLECT_INFO()

Dim i As Integer
Dim Weld As Variant, Elect As Variant, Paint As Variant, Assem As Variant
Dim wb1 As Workbook
Dim startd As String
Dim ARC_NUM As String
Dim CALDates As Range
Dim EVARCs As Range


Set wb1 = Workbooks("EV ARC Master List (version 1).xlsx")


startd = wb1.Sheets("Master List").Cells(4, 10)


ARC_NUM = wb1.Sheets("Master List").Cells(4, 2).Value


Workbooks("new calendar.xlsm").Activate


Weld = Sheets("Projects").Cells(19, 4).Value
Elect = Sheets("projects").Cells(20, 4).Value
Paint = Sheets("projects").Cells(21, 4).Value
Assem = Sheets("projects").Cells(22, 4).Value


Set EVARCs = Sheets("calendar").Range("B3:ZZ3")
Set CALDates = Sheets("calendar").Range("A4:A34")


Dim fndcol As Range
Dim fndrow As Range


' Find column


Set fndcol = EVARCs.Find(ARC_NUM, lookat:=xlWhole)


'find row


Set fndrow = CALDates.Find(startd, LookIn:=xlValues, lookat:=xlPart)


'find intersection


Application.Goto reference:=Cells(fndrow.Row, fndcol.Column).Activate
ActiveCell.Insert ("Project Start")


End Sub

 

[Michael M]

If you step through the code manually, then hover over fndCol and fndRow, what does it say the values are ??
Also, what is this, text, a worksheet, a named range ???

ActiveCell.Insert ("Project Start")

 

[wsk92]

hi michael,

The fndcol comes out with 2 (which is correct), but fndrow comes out with nothing, i have tried various other functions and cant seem to make it work. I was wondering whether it was to do with the formatting of the dates, but i think they are fine.

Also its a text at the bottom! i should say, I am very new at vba!

 

[Michael M]

If you are using this line

Set fndrow = CALDates.Find(startd, LookIn:=xlValues, lookat:=xlPart)

Is it possible there is no Caldates in the search range ?
AND
this line should be

ActiveCell.Insert ("Project Start")

to

ActiveCell.value="Project Start"

I think a better option would have been to tell us what you were trying to do and let us present the best code for you....then you could learn from that !!