PDA

View Full Version : VBA help needed :(



mercmannick
05-14-2009, 10:57 AM
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


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

:banghead:

Simon Lloyd
05-14-2009, 11:27 AM
Are you checking the sum value or do you want to check the single cell(s) values in your condition?

mercmannick
05-14-2009, 11:34 AM
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
05-14-2009, 11:53 AM
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
05-14-2009, 11:56 AM
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
05-14-2009, 12:27 PM
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
05-14-2009, 12:41 PM
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
05-14-2009, 12:50 PM
Ooops sorry caps was on :(

Pic Attached

Nick

Simon Lloyd
05-14-2009, 02:27 PM
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
05-14-2009, 02:31 PM
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:
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

mercmannick
05-14-2009, 04:07 PM
hi simon , thanks for help i get error on this line 1004

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

cnt get hlookup value of worksheet function

any ideas many thenks

Nick

Simon Lloyd
05-14-2009, 04:12 PM
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
05-15-2009, 07:54 AM
Hi simon tryed it with sample also and it fills 1 and 2's in range b6:x9

Regards

Nick

Simon Lloyd
05-15-2009, 11:47 AM
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
05-21-2009, 08:19 AM
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 :thumb

Thanks

Nick