| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a Macro
I can't make the Data_value work
for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP in a Macro
Countif is probably a better choice than Vlookup. countif is faster for one
thing and a little easier to implement for I = 170 to 182 IF Application.Countif(DayNr,I)<0 THEN something ENDIF Next HTH "Peter Hesselager" wrote: I can't make the Data_value work for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP in a Macro
You're just the guy !
I only had to use the Range-command to avoid a Type Mismatch - like this If Application.CountIf(Range("DagNr"), I) < 0 Then -eeh, now can you also tell me how to go to the A column on the actual Row ? I Have tried Selection.End(xlToLeft).Select but some cells in the row may be empty - so it's not a sure thing "Jim Thomlinson" wrote: Countif is probably a better choice than Vlookup. countif is faster for one thing and a little easier to implement for I = 170 to 182 IF Application.Countif(DayNr,I)<0 THEN something ENDIF Next HTH "Peter Hesselager" wrote: I can't make the Data_value work for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP in a Macro
Sorry I did not know if DagNr was a range object or a named range. There are
a couple of possible solutions to get the A Column. Since I don't know if Selection is a single Cell or a range of cells and I don't know if you want to select the A Cells I will give you the full blown solution... dim rng as range set rng = intersect(activesheet.range("A:A"), selection.entirecolumn) 'You can now just use rng like any range of cells rng.select HTH "Peter Hesselager" wrote: You're just the guy ! I only had to use the Range-command to avoid a Type Mismatch - like this If Application.CountIf(Range("DagNr"), I) < 0 Then -eeh, now can you also tell me how to go to the A column on the actual Row ? I Have tried Selection.End(xlToLeft).Select but some cells in the row may be empty - so it's not a sure thing "Jim Thomlinson" wrote: Countif is probably a better choice than Vlookup. countif is faster for one thing and a little easier to implement for I = 170 to 182 IF Application.Countif(DayNr,I)<0 THEN something ENDIF Next HTH "Peter Hesselager" wrote: I can't make the Data_value work for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP in a Macro
Oops... change
set rng = intersect(activesheet.range("A:A"), selection.entirecolumn) to set rng = intersect(activesheet.range("A:A"), selection.entirerow) Dopey me... "Jim Thomlinson" wrote: Sorry I did not know if DagNr was a range object or a named range. There are a couple of possible solutions to get the A Column. Since I don't know if Selection is a single Cell or a range of cells and I don't know if you want to select the A Cells I will give you the full blown solution... dim rng as range set rng = intersect(activesheet.range("A:A"), selection.entirecolumn) 'You can now just use rng like any range of cells rng.select HTH "Peter Hesselager" wrote: You're just the guy ! I only had to use the Range-command to avoid a Type Mismatch - like this If Application.CountIf(Range("DagNr"), I) < 0 Then -eeh, now can you also tell me how to go to the A column on the actual Row ? I Have tried Selection.End(xlToLeft).Select but some cells in the row may be empty - so it's not a sure thing "Jim Thomlinson" wrote: Countif is probably a better choice than Vlookup. countif is faster for one thing and a little easier to implement for I = 170 to 182 IF Application.Countif(DayNr,I)<0 THEN something ENDIF Next HTH "Peter Hesselager" wrote: I can't make the Data_value work for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting the leftmost cell on a row
Hi Jim !
I only got to this issue now ( the CountIf works just fine now). Sorry, that I wasn't more detailed in my former question I have to go to the A cell on the actual row. The user may stand on any cell on a ROW, when he activates the Macro. So - to have a well defined activeCell, from which I can reach out with Offset commands - I want to activate the very leftmost cell on the row. The macro only deals with values on the actual row - anyway - i'll look into it now "Jim Thomlinson" wrote: Oops... change set rng = intersect(activesheet.range("A:A"), selection.entirecolumn) to set rng = intersect(activesheet.range("A:A"), selection.entirerow) Dopey me... "Jim Thomlinson" wrote: Sorry I did not know if DagNr was a range object or a named range. There are a couple of possible solutions to get the A Column. Since I don't know if Selection is a single Cell or a range of cells and I don't know if you want to select the A Cells I will give you the full blown solution... dim rng as range set rng = intersect(activesheet.range("A:A"), selection.entirecolumn) 'You can now just use rng like any range of cells rng.select HTH "Peter Hesselager" wrote: You're just the guy ! I only had to use the Range-command to avoid a Type Mismatch - like this If Application.CountIf(Range("DagNr"), I) < 0 Then -eeh, now can you also tell me how to go to the A column on the actual Row ? I Have tried Selection.End(xlToLeft).Select but some cells in the row may be empty - so it's not a sure thing "Jim Thomlinson" wrote: Countif is probably a better choice than Vlookup. countif is faster for one thing and a little easier to implement for I = 170 to 182 IF Application.Countif(DayNr,I)<0 THEN something ENDIF Next HTH "Peter Hesselager" wrote: I can't make the Data_value work for I = 170 to 182 IF I = VLOOKUP(I, DayNr,1) THEN something ENDIF Next I = actual daynumber DayNr is a range of Working day No. If I substitute "I" with a value ( say 170), it works OK. The goal is to decide, whether I is a working day or not. How should it be written ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - Vlookup | Excel Discussion (Misc queries) | |||
| vlookup macro | New Users to Excel | |||
| VLOOKUP in a macro?? | Excel Discussion (Misc queries) | |||
| VLOOKUP MACRO | Excel Programming | |||
| Need a macro to do what this VLOOKUP does | Excel Programming | |||
Linear Mode
