Using a function in VBA across a range

[Calum]

I need to pull in two values across two cells on a source document and include it on a column header on a worksheet:

Workbooks(today_name).Worksheets(5).Activate
date_today = Range("E3").Value
file_name = Range("E1:E2").Value
Workbooks(wkbk_name).Worksheets("Provision").Activate
Range("B27").Value = Range("B27").Value & file_name & " " & date_today

It does not like the third line with the range value across a range of two cells and comes back as a type mismatch.

Any help would be most appreciated!

 

[dmt32]

Hi,

try changing this line:

Range("B27").Value = Range("B27").Value & file_name & " " & date_today

to this:

Range("B27").Value = Range("B27").Value & file_name(1, 1) & file_name(2, 1) & " " & date_today

Dave

 

[Calum]

Hi Dave,

None of the code now runs with the edit above. It skips straight to "Compile error: Expected array"

Do I need to declare anything beforehand?

 

[dmt32]

Hi Dave,

None of the code now runs with the edit above. It skips straight to "Compile error: Expected array"

your your variable file_name should be dimensioned as a variant.

Dave