Posts by richadj4

Re: VLOOKUP Exchange Rate from 12 different Workbooks Depending Upon Month Column

Hmmmm.
Not ideal. a couple of options (NOT great options, but options)
1) a REALLY REALLY HORRIBLE choose function or bunch of if functions. This should theoretically work, but just thinking about it makes me throw up a little
2) 12 hidden columns - each hidden column pulls in the exchange rate for 1 month, and then you can use a formulae to pick the value in the appropriate column to display in the visible column
3) VBA - a UDF could accomplish this without too much difficultly, are macros "allowable" at your company?

Really the best solution is (as Windy58 said) to consolidate the workbooks.

If any of the above 3 sound appropriate, post back and I can give more help on the specific one

Re: Printing Excel Reports via a Word Document

ummmm yeah.
1) "print my excel reports through word document" - Don't know what this means.
2) "I don't know how to use VBA" - Well, that could be a problem. I am 99% confident that yes this (whatever it actually is) could be done with "some macros in VBA", but basically you are asking someone to completely write a project for you with the vaguest of descriptions. I'm not saying no-one here WILL, but I certainly won't.

Re: vba to COLORIZE NUMBERS in a Column NOT= to XXXXXX (any cell with 6 char long)

muhahahahahahahahaha
Issue is with counting
PDN colon space is FIVE characters not 6.
so you need to start from the SIXTH character, not the SEVENTH.
simply replace

myValue = Mid(.Range("A1").Value, 7, 6)

with

myValue = Mid(.Range("A1").Value, 6, 6)

Re: Runtime error 1004 - unable to get the autocomplete property of the range class

On the bold line (which by the way doesn't work in code blocks). orange is a range of cells the size of list_name. autocomplete only works on a single cell, so you need to add the .cells(1,1) to the orange object.

Re: Paste to NEXT AVAILABLE UNUSED BLANK SHEET in same workbook - vba

several different ways, probably the easiest to understand is

set wsChosen = nothing
for each ws in sheets
if ws.cells(65000,1).end(xlup).row = 1 then set wsChosen = ws
next
if wsChosen is nothing then
'No blank sheets, whatcha gonna do
end if

Re: Generating DNR using OFFSET and COUNTA when column has blank cells between data

instead of counta(Sheet1!$B:$B), use
MAX(IFERROR(MATCH("*",$B:$B,-1),0),IFERROR(MATCH(9E+307,$B:$B,1),0))

This returns the row number of the last cell in the column with a value, so the number of blanks is irrelevant.

Re: Advanced Filter a data list by name criteria when two names have same beginning!

It SHOULD work on MAC. Can you upload the spreadsheet? or a sample?

Re: Regex expression to retrieve data from HTML

Range("K4").Offset(count, 0) = RegMatch.submatches(0)

the regmatch contains the full match object, submatches(0) contains the portion of the match inside the ().

Re: mousewheel Scrolling in userform

Yes, works flawlessly in 2007 32 bit and 2010 32 bit, on windows 7 32 bit, window7 64 bit and windows 8.1 64 bit. I'm 99%+ sure that the issue is with 64 bit office and 80%+ that it relates to the formMouseProc definition, most likely in the MouseHookStruct Type parameter.

If you want to use the above in 32 bit office, it should work fine, just pass the userform in question to the wheelhook function, and remember to unhook when done. The actual functionality is done by sending a pgdown/pgup key request to the form

Re: use formula to set criteria and run advanced filter, not working, only heading ap

looks like it should "work", possible your criteria does not actually return any results. Can you attach a sample?

Re: Advanced Filter a data list by name criteria when two names have same beginning!

In the criteria range instead of entering Michel in the cell enter ="Michel"
(with the = symbol and the ")
this prevents partial matches

Re: VBA identifying row by more than one criteria

Firstly you should not need to actually activate ANY sheets.
where you code such as

Sheets("Master").Activate 
         
        With Range("A:A") 
            Set LastCell = .Cells(.Cells.Count) 
        End With 
         
        Set FoundCell = Range("A:A").Find(what:=NINO, after:=LastCell) 
.....

Do NOT activate the sheet, instead prefex the range command with the sheet such as

With Sheets("Master").Range("A:A") 
            Set LastCell = .Cells(.Cells.Count) 
        End With 
         
        Set FoundCell = Sheets("Master").Range("A:A").Find(what:=NINO, after:=LastCell)

Re: mousewheel Scrolling in userform

24 hour bump
(Couldn't see anything in the forum rules about appropriate bump times, if 24 hours is too soon, apologies)

Re: Tab into Listbox

Firstly, Domenic's solution is MUCH better.

However the error in mine is that you have not declared cLast. You need a line at the top of the module

private cLast as range

Re: Regex expression to retrieve data from HTML

Hmmmmm, just added a

set ie= new internetexplorer

line and yeah, infinite loop. On my PC (and probably yours) the problem is the invisible ie object spams an invisible "Welcome to internet explorer" popup, which PREVENTS the conditions on your do line being true until the invisible popup is dealt with.

Thanks Microsoft, real helpful.

instead of accessing the html via ie object you may need to access it via xmlhttprequest object.

Re: Regex expression to retrieve data from HTML

Okay, I've just tested that and the regexp portion is fine, so something is wrong with your IE code. what is IE and where is it defined? public variable? (the code as-is will not compile)

Re: Regex expression to retrieve data from HTML

that's what it is SUPPOSED to do, but you've stated "it's not really working", so what is it ACTUALLY doing?

Re: Subtract x Hours from Cell that Has both date and time

If it is ACTUALLY a time (unlikely) then just =a1-time(3,0,0) works.
If it is just text, then you need to convert it into a date time, subtract 3 hours and convert back, kinda like:
=TEXT((DATEVALUE(MID(A1,SEARCH("z",SUBSTITUTE(M73," ","z",2))+1,SEARCH("z",SUBSTITUTE(M73," ","z",3))-(SEARCH("z",SUBSTITUTE(M73," ","z",2))+1))&"/"&MID(A1,5,3)&"/"&MID(A1,SEARCH("z",SUBSTITUTE(M73," ","z",3))+1,4))+TIMEVALUE(MID(A1,SEARCH("z",SUBSTITUTE(M73," ","z",3))+6,10))-TIME(3,0,0)),"ddd mmm mm yyyy h:mm AM/PM")

Re: Regex expression to retrieve data from HTML

"Not really working". What exactly is it doing?

Re: Searching for a string or a part of string inside a string variable

Couple of examples needed. You USED to be comparing bob-RXX in the listbox with bob-RXX on the worksheet, and now you are comparing ???????????? in the listbox with bob-RXX on the worksheet?