CODEWITHSUNDEEP
excelvba
Andrew G
Andrew G

Reputation: 3

Writing Ranges into formulas with VBA

I am using variable ranges, and want to insert those ranges into a formula that can be saved in a document that does not use VBA.

Do I have to use a with statement?

My If Statement works until I try and insert a relative range. I searched and didn't see much on this, so this issue is probably so simply I am an idiot.

The program will be doing things all over the worksheet, so relative coding is needed. The number of items in the range changes with each iteration. I will be using an end range integer [myRange] for the number instead of 10, but I wanted to simplify what I am struggling with into the smallest steps.

    Dim Test As String
    Test = "=IF(COUNTIF(" & ActiveCell.Offset(1, 0).Address & ":" & ActiveCell.Offset(10, 0).Address & "" _
            & ")," & """Not Complete""" & "," & "" & """"")"
    ActiveCell.Formula = Test

Desired output in target cell: "=IF(COUNTIF($J$3:$J$12),"Not Complete","")"

Upvotes: 0

Views: 55

Answers (1)

urdearboy
urdearboy

Reputation: 14580

As mentioned in comments, your COUNTIF statement is currently invalid. What are you counting in the range J3:J12? This needs to be added (in place of "CRITERIA" in below solution) before your equation will be valid

Test = "=IF(COUNTIF(" _
        & ActiveCell.Offset(1).Resize(10).Address(True, True) _
        & ",""CRITERIA""), ""Not Complete"", """")"

If you want your output to actually have the quotes you can output Chr(34) & Test & Chr(34) which seems cleaner than wrapping the whole string in quotes from a readability point of view IMO

Upvotes: 1

Related Questions