vba - I want to use a variable which contains a cell reference within a formula -

vba - I want to use a variable which contains a cell reference within a formula -

i have been trying code out , if utilize direct cell reference in formula works fine when substitute cell reference variable doesn't work. can tell me im going wrong. aim add together cells contain date in november

this code

private sub worksheet_change(byval target range) dim lastly long dim cussat variant dim cussatrange string activesheet lastly = .cells(.rows.count, "a").end(xlup).row end if target.address = "$c$" & lastly + 1 range("$b$" & lastly + 1).value = date range("$a$" & lastly + 1).value = "moss" cussatrange = "j1:j" & lastly ' substitue cell reference in above formula utilize cussatrange or lastly cussat = [=sumproduct(--(text(j1:j43,"mmm yyyy")="nov 2014"))] msgbox "last used row number in column " & lastly & " " & cussat & " " & cussatrange end if end sub

if understand question correctly, can utilize this:

dim myformula string dim template string template = "=sumproduct(--(text({0},""mmm yyyy"")=""nov 2014""))" myformula = replace(template, "{0}", cussatrange) cussat = application.evaluate(myformula) msgbox "last used row number in column " & lastly & " " & cussat & " " & cussatrange

in code template formula created , substring (in case {0}) repalaced address stored in string cussatrange.

for more info application.evaluate method have here: http://msdn.microsoft.com/en-us/library/office/ff193019%28v=office.15%29.aspx. (using square brackets (for example, "[a1:c5]") identical calling evaluate method string argument)

vba excel-vba