Help with VBA code please

ciavala

Board Regular
Joined
Dec 7, 2003
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

Board Regular
Joined
Dec 7, 2003
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

Board Regular
Joined
Dec 7, 2003
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 7, 2003
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top