PLEASE HELP!! finding intersection between two variable and inserting a value

[wsk92]

I've been trying for a couple weeks to create a vba that will fill out my project calendar for me. First thing i am trying to do is have the vba find the intersection between two variable and insert a string into the intersecting cell.

I have tried various things such as match, vlookup and find functions but i think i may be doing something slightly wrong....below is my various variables and an attempt to find the intersection.

Mainly having issues finding the start date from the calendar. the vba can find the startd but cannot seem to match it with a date in the calendar. and the application.goto line has always come up with an error saying object required.

ANY HELP IS MUCH APPRECIATED.

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 Date ' this is the start date
Dim ARC_NUM As String ' this is the project number
Dim CALDates As Range ' this is the calendar dates
Dim EVARCs As Range ' this is the project numbers on the calendar
Dim finalrow As Integer


i = 4


Set wb1 = Application.Workbooks("EV ARC Master List (version macro).xlsm")


ARC_NUM = wb1.Sheets("Master List").cells(i, 2).Value


Workbooks("new calendar.xlsm").Activate


finalrow = Sheets("Calendar").Range("A35").End(xlUp).Row
startd = Sheets("Projects").cells(3, 5).Value
startd = Format(startd, "dd/mm/yyyy")


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 = fndrange.Find(ARC_NUM, LookIn:=xlValues, lookat:=xlWhole)


'find row


'fndrange = Application.WorksheetFunction.VLookup(startd, Range("A1:A34"), fndcol.Column, False)


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


'find intersection


Application.Goto reference:=Worksheets("calendar").Range(fndrow.Row, fndcol.Column).Activate
ActiveCell.Value = "Project Start"


End Sub

 

[Michael M]

Duplicate post

http://www.mrexcel.com/forum/excel-questions/868003-object-required.html

 

[Smitty]

Please stick to your Original Thread.