Posts by GrahamB

    Thanks for the response,

    Unfortunately no, I will be seeking up locate and interogate files that may have had name changes done by customers.

    I initially was going to put the VBA in each of those customer files but this maybe too cumbersome, I will re think that, but would still like to work this out as well.


    Afternoon all,

    Dave has helped to get me this far, I can find a file on c:\ drive, I can isolate the path name etc, my next challenge is to list all the worksheet names (eg Sheet1, Sheet 2 etc) from the file while it is still closed. The code so far is......

    Sub FindIt()
    Dim filnam
    Dim filpatnam
    filnam = [a1] ' Determine what file is required

    With Application.FileSearch
    .LookIn = "C:\"
    .SearchSubFolders = True
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = filnam
    If .Execute > 0 Then
    MsgBox Replace(.FoundFiles(1), filnam, ""), , "BPL"
    [a2] = Replace(.FoundFiles(1), filnam, "") & filnam
    MsgBox "File Not Found", , "BPL"
    End If
    End With
    ' lock down file path and name
    filpatnam = Replace(.FoundFiles(1), filnam, "") & filnam
    ' list the worksheet names
    [code to loop thru' file, collect names and copy to cells b1:b'x']
    End Sub

    Does this make sense?



    Afternoon All,

    If I know the name of a file but not the path of where it is located, is there a method by which I can search for it?

    eg file name AAAA.xls is known but path c:\Docus\worksheet is not known.

    A conundrum?


    Evening All,

    I have seen this done but cannot find it

    In cell a1 I have a name eg Joe Bloggs

    In cell d1 I would like vba to put "Bloggs" (with the "s)
    in cell e1 JB (without the "s)

    For those who know from me who don't,



    Hi All,

    I am a total novice when it comes to Web based Excel.

    I have a spreadsheet with a lot of VBA code. I would like to put this spreadsheet on the web so others could input information, submit it, the spreadsheet would do it's thing and update an output field on the web.

    At no time should those on the web see the spreadsheet formulas or VBA code.

    IS this possible? If so how?



    I have rejigged the code to read - thus selecting a specific cell and it works perfectly. Many thanks.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lun As String
    If [d5] = 0 Or [d5] = "" Then Exit Sub
    On Error GoTo Oops
    lun = WorksheetFunction.VLookup(Target.Value, Range("Name"), 2, False)
    [d5] = lun
    End Sub


    Much thanks, I have had a quick play and looks good - I will need to address it to a particular cell rather than a general 'any cell' change condition -

    Again thanks,


    Morning All,

    I am trying to use the following code to automatically input a name eg if I put in "a" it will over write the "a" with "Adelaide" etc

    a Adelaide
    b Brisbane
    c Canberra
    d Darwin
    e Echuca
    f Fremantle
    g Greater Melbourne
    h Hobart
    i Ivanhoe

    the code I am using is on the worksheet

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lun As String
    If [b2] = 0 Or [b2] = "" Then
    Exit Sub
    lun = WorksheetFunction.VLookup([b2], Range("Name"), 2, False)
    [b2] = lun
    End If
    End Sub

    (lun means lookupname, "Name" is the range covering "a...Ivanhoe")

    The error returned is "Runtime error '1004': Unable to get the VLookup property of the WorksheetFunction Class"

    I have looked and looked and cannot see what I have done wrong - can anyone assist, I would be most greatful.


    Good thought Dangelor,

    However, if I can toggle it up or down then it can become standard code for any worksheet.

    For eveything I write, all standard codes go into one module & all specifics go into another.

    I will continue seeking a solution

    Thanks again,


    Morning All,

    I seem to be getting simple problems with solutions I cannot work out.

    I would like to create a macro to toggle up and down the zoom function. In my example "start" is 100 and put into a range name that is set every time the file is opened. Then alt u would toggle the zoom up by 5% each time it is pressed and alt d down 5%. Any ideas?

    Sub up()
    Dim start
    start = start + 5
    ActiveWindow.Zoom = start
    End Sub

    Sub down()
    Dim start
    start = start + 5
    ActiveWindow.Zoom = "start"
    End Sub

    Thanks Roy, Richie,

    I was actually looking at another question and I modified something Roy was doing for another chap, this is my result

    Sub relativerange()
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(5, 4)).Select
    End Sub

    I tried to get back on last night but my ISP went pearshaped.

    Again thanks queried answered!


    Thanks Roy,

    Doesn't that just move the activecell down 5 rows, my aim is to select the range from the initial active cell to 5 rows.

    Or have I missed something?


    Evening All,

    This has to be simple, but I cannot for the life of me get it right....

    When writing a macro to select a range it is as easy as [a1:b5].select

    I want to be able to select the range by writing in the code (without being specific of the cell address that is I want to do it by relative addresses)


    move the next cell to the right
    select from the active cell to 5 cells down.

    Does this make sense?




    I am nesting Vlookups often - your forumla looks ok but there maybe a number of things you can do to enhance it.

    - put your ranges in as names rather than 'sheets'1!$c$2 etc it is easier to track problems
    - add 'false' as the last part of the formula - this will look for the exact match; the way it is now it will look for the first nearest

    =VLOOKUP(A2,'04-01 to 04-30'!$C$2:$AW$600,3,false)+(VLOOKUP(A2,'05-01 to 05-31'!$C$2:$AW$600,3,false))

    - if you don't want to use the false element, make sure your $C$2:$AW$600 is sorted according to your selection criterion. This means it will track down the range until it comes to the closest.

    I prefer the exact method, that way you can be sure of the results.

    Hope this helps.


    Gidday Samurai,

    Sorry for the delayed response - my computer blew up last night (my fault)

    Add the following code after "Sub copybelow()" line

    if [b72]<>"" then
    answer = msgbox("You are about to enter data beyond the last line, do you want to continue?",Buttons:=vbYesNo+vbQuestion)
    if answer = vbNo then
    Exit Sub
    End if
    End if

    Hope this helps,


    ps I have not tested it but it reads ok to me