Help with VBA code please

[ciavala]

I’m working with this code as part of a different worksheet, trying to see if it will work. I finally got it to work and then all of a sudden it stops working telling me my formula is blank when I debug. I have various vac, off, and 1’s in range A1:A4 --------Very confused?????


Private Sub CommandButton1_Click()

Dim rowfirst As Variant
Dim rowlast As Variant

Range("a1:a4").Select
rowfirst = ActiveCell.Address

Range(ActiveCell.Address).End(xlDown).Select
rowlast = ActiveCell.Address

MsgBox "first address" & rowfirst
MsgBox "lastaddress" & rowlast
MsgBox "Range" & rowfirst & ":" & rowlast

If Range("e12").Formula = "=SUMPRODUCT(COUNTIF(rowfirst & : & rowlast,{""vac"",""ptk"",""1"",""sck"",""off""})*{1,1,0.25,1,1})" >= 3 Then


MsgBox "CAN NOT WORK"

Else
MsgBox "CAN WORK"
End If
End Sub

 

[Tom Urtis]

I think it's because you are making a false assumption about the formula in E12, which can never look in the cell the way you say it should look in your macro, because you are using quotes in your macro to describe the actual look of the cell's formula, when really E12's formula will not have the VBA variables rowfirst and rowlast in it.

One way to go about this if I follow you, is to evaluate in VBA what E12 would return if it had that formula, and if it is greater than 3, continue on with your conditional code.

One other thing, VBA does not like those curly braces in its formula code, such as you find in constant arrays of a VLOOKUP worksheet formula. I'm not talking about manufactured braces in array formulas, but only the ones truly manually entered such as your nested COUNTIF suggests, which is also part of the problem you may not have been aware of.

 

[ciavala]

Hello Tom,

It’s ciavala from the Excel board with the sumproduct question. I just tried to stick the sumproduct formula in cell E12 because I don’t know how to make that formula execute in the macro. The rowfirst and rowlast have to be dynamic - the range will be chosen by the user when they pick a certain day of the year. By the way thanks again, you helped me get that active x calendar control working. Is it possible to make my range with two dynamic variables and insert them into the formula on the fly so I can get the results and make a decision of whether someone will be available to work or not. Did I make any sense here? What I’m trying to evaluate is whether someone is just off, on vacation, or personal leave with the sumproduct formula. A sum of >=3 will mean they are not available to work. Am I in over my head here????


Larry

 

[Tom Urtis]

I'm sure it would be possible, but can you help the readers of this thread, including me, understand your logic of the following code block:

Range("a1:a4").Select
rowfirst = ActiveCell.Address

Range(ActiveCell.Address).End(xlDown).Select
rowlast = ActiveCell.Address


As long as you are selecting A1:A4 in the code, my 2 questions here are...

(1)
Why bother declaring a first row variable for the active cell, since it will always be row 1 because A1 is the active cell of your coded selection at that moment?

(2)
Why are you selecting a four-cell vertical range and then looking downward? I don't understand that.

 

[ciavala]

I’m back – NE 21 Philly 14

Actually Column A in my worksheet is all the days of the year. Depending on which day is selected will determine the actual dynamic range selected. Previous to this code the user will have selected a persons name and a date. Dates are column A the persons name will determine the other dynamic column letter.
So Range ("a1:a4"). Select rowfirst = ActiveCell.Address could actually be
Range ("A64:C64"). Select rowfirst

Range (ActiveCell.Address). End (xlDown). Select
rowlast = ActiveCell.Address then selects the dynamic range for me.

I determined that a four row vertical selection with all possible variations if >=3 can determine is an employee is available to work or not and then I will shade that persons name off the calling list which is another worksheet.

From column’s A date selected, I will have to offset and search all employees (12 columns) across that row and values less than 3 will not be shaded and called to fill the shift. Am I making sense now?

 

[ciavala]

I finally got it to work!!!!

Thanks Tom for steering me in right direction.

I put the formula into an activecell and let the formula fire – then used the value of the activecell in the if statement to retrieve my sumproduct value. Working code:

Private Sub CommandButton1_Click()

Dim rowfirst As Variant
Dim rowlast As Variant


Range("a1:a4").Select
rowfirst = ActiveCell.Address

Range(ActiveCell.Address).End(xlDown).Select
rowlast = ActiveCell.Address

MsgBox "Range" & rowfirst & ":" & rowlast

Range("e13").Select

ActiveCell.Formula = "=SUMPRODUCT(COUNTIF(A1:A4,{""vac"",""ptk"",""1"",""sck"",""off""})*{1,1,0.25,1,1})"

If ActiveCell.Value >= 3 Then
MsgBox "CAN NOT WORK"
Else
MsgBox "CAN WORK"
End If
End Sub