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.

GB

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
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = filnam
If .Execute > 0 Then
MsgBox Replace(.FoundFiles(1), filnam, ""), , "BPL"
[a2] = Replace(.FoundFiles(1), filnam, "") & filnam
Else
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?

TIA,

GrahamB

Thanks Dave,

The more I see, the more I realise I don't see,

Graham B

Thanks Justin,

Actually looking for the VBA to do it,

KR GB

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?

GrahamB

TJ,

I should have known better, many thanks

Kind regards

GB

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,

Thanks

GrahamB

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?

GB

thomach,

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

GrahamB

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
Oops:
End Sub

thomach,

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,

GB

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
Else
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.

GB

Pesky,

That is a gem of a result

Gb

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,

GB

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

And its older then me!

LOL

G

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!

GB

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?

GB

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)

eg

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

Does this make sense?

TIA

GB

A84,

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.

GB

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
Else
End if
End if

Hope this helps,

GB

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