VBA help needed :(

[mercmannick]

[VBA] Dim MyRng, Cell, CELLS As Range
Dim HRRow, OPCol, StCol, FINCol, RollNo As Range
Dim a, b, c, d, e, f As Long
Set MyRng = ActiveSheet.Range("T7:AL25") ' where i need 1 or 2 inserted depending if B7:B25 has EMPTY IN
Set RollNo = ActiveSheet.Range("B7:B25") 'COL B = ROLL NO
Set HRRow = ActiveSheet.Range("l5:gy5") 'L5:GY5 = HOUR LINE
Set FINCol = ActiveSheet.Range("k7:k25") 'COL K = FIN HOUR
Set OPCol = ActiveSheet.Range("i7:i25") 'COL I = HOURS OP PER ROLL
Set StCol = ActiveSheet.Range("j7:j25") 'COL J = START HOUR
b = HRRow
c = StCol
d = FINCol
For Each a In RollNo
If a.Value = "EMPTY" And b.Value >= c.Value And b.Value <= d.Value Then
MyRng.Value = 2
End If
Next a
'IF(AND(hrrow >= stcol,hrrow <= fincol),2,"")


'IF(AND(hrrow >= stcol,hrrow <= fincol),1,"")



End Sub
'=IF($B7="EMPTY",IF(AND(hrrow >= stcol,hrrow <= fincol),2,""),IF(AND(T$5>=$J7,T$5<=$K7),1,"")) ACTUAL FORMULA IN EACH CELL
[/VBA]

i am trying to check with this formula above in VBA : Basically if col B7:B25 is greater than "" then IF(AND(hrrow >= stcol,hrrow <= fincol),1,"")

if B7:B25 is greater than blank and = "EMPTY" then this

IF(AND(hrrow >= stcol,hrrow <= fincol),2,"")

can anyone tell me where i am going wrong please

Thanks

Nick

[Simon Lloyd]

Are you checking the sum value or do you want to check the single cell(s) values in your condition?

[mercmannick]

hi Simon,

The single cell VALUE as if statement is true then it enters a 1 or 2 or "" in each cell

Thanks

Nick

[Simon Lloyd]

I meant when looking at your ranges like b.Value? do you want to check each of the cells in each of the ranges to see if they have your criteria? lets say you are checking A7 do you want to check for your criteria in each cell of your other ranges or just the offset from A1 i.e B1?, also you have declared a as long not a range so For Each a.Value will equate to nothing, a should be declared as Range.

[mercmannick]

Hi Simon

i am not explaining properly , attached is sample workbook with how i am trying to get VBA to do same

it will make more sense if you see it

Thanks for your help

Nick

[Simon Lloyd]

It still doesn't make sense, do you want to get rid of the formulae? as entering 1 or 2 in MyRng (which is Range("T7:AL25") ) will populate that entire range with 1's or 2's - can you explain what you want to see happen and why, don't try to explain code or formula wise just what you want to see happen and when.

[mercmannick]

If there is roll in ColB then show from start time to end (ie 25hrs) and highlight relevant cells in range (MyRng) any colour,

Same above but if ColB says empty then highlight different Colour

Yes want to get rid of formulas as I have approx 23 sheets all with about 4 or 7 different machines on, which makes workbook to big.

Was hoping to get 1 range working and then apply to rest

If you look at sample that's exactly how I need it but minus the formulas

Thanks again

Nick

Edited by Aussiebear: I'm halfway around the world and its still ringing in my ears :-(

[mercmannick]

Ooops sorry caps was on

Pic Attached

Nick

[Simon Lloyd]

I have done the code for what you need except your columns and figures are out!, you check against column J but there are no figures in there, what is the criteria for filling the cells with regards to hours?

[Simon Lloyd]

I have done the code for what you need except your columns and figures are out!, you check against column J but there are no figures in there, what is the criteria for filling the cells with regards to hours?

Leaving your criteria figures aside this code does what you need:
[vba]Sub mychange()
Dim a As Range, RollNo As Range
Dim rFound As Long, r1Found As Long
Dim r, r1
Set RollNo = ActiveSheet.Range("B6:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each a In RollNo
If LCase(a.Value) = LCase("Empty") Then
r = Application.WorksheetFunction.HLookup(a.Offset(0, 10).Value, Range("N5:AO5"), 1, True)
rFound = ActiveSheet.Rows("5:5").Find(What:=r, After:=Range("M5"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
r1 = Application.WorksheetFunction.HLookup(a.Offset(0, 11).Value, Range("N5:AO5"), 1, True)
r1Found = ActiveSheet.Rows("5:5").Find(What:=r1, After:=Range("M5"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
Range(CELLS(a.Row, rFound), CELLS(a.Row, r1Found)).Value = 2
ElseIf LCase(a.Value) <> LCase("Empty") Then
r = Application.WorksheetFunction.HLookup(a.Offset(0, 10).Value, Range("N5:AO5"), 1, True)
rFound = ActiveSheet.Rows("5:5").Find(What:=r, After:=Range("M5"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
r1 = Application.WorksheetFunction.HLookup(a.Offset(0, 11).Value, Range("N5:AO5"), 1, True)
r1Found = ActiveSheet.Rows("5:5").Find(What:=r1, After:=Range("M5"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
Range(CELLS(a.Row, rFound), CELLS(a.Row, r1Found)).Value = 1
End If
Next
End Sub[/vba]

[mercmannick]

hi simon , thanks for help i get error on this line 1004

[VBA] r = Application.WorksheetFunction.HLookup(a.Offset(0, 10).Value, Range("N5:AO5"), 1, True)[/VBA]

cnt get hlookup value of worksheet function

any ideas many thenks

Nick

[Simon Lloyd]

Maybe you are trying the code on a workbook that does not have the same structure as the sample you sent....i just tried that code again and it worked perfect!

[mercmannick]

Hi simon tryed it with sample also and it fills 1 and 2's in range b6:x9

Regards

Nick

[Simon Lloyd]

It should have filled past column x, run the code in the attached in the SimonCode module, you should find it works perfect (i just tried it again!).

[mercmannick]

Simon i found out it was syntax in HLOOKUP formula as im using office 2000 in work , thanks for your help it works fantastic.

if i wanted this to work on a couple of arrays ie:B7:B45,B50:B67 ETC

How would i amend this to suit

Thanks

Nick