Posts by Lippa

Re: Defined range won't work for Find method

Thats really strange as the data was definitely contained within the range, I also tried much larger ranges which encompassed all the data, it wasn't the name of the sheet as it was the active sheet and failed when I removed the sheets("Address Database") as well. I must have had a silly mistake somewhere, but I like your abbreviated version better anyhow, so its a win Thanks again

Re: Defined range won't work for Find method

Thanks Smallman, that worked I appreciate that much simpler method!

Although I still dont understand why using the other method it worked with "cells." but not with the "range()" and "Address Database" was the active sheet.

Thanks again

Hello good people :),

Just wondering if anyone could please help me with a problem I have with the find method in a macro I am using. If I use the following line it will work :-

FoundNumRow = Cells.Find(What:=Phone_Number, After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row

however if I try to define a smaller range for it to search it wont find the search string.

FoundNumRow = Sheets("Address Database").range("h1:l1000").Find(What:=Phone_Number, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row

I am using the correct name for the Worksheet, with the capital letters in the correct places and the data I am searching for is definitely contained within the range specified.

I would really appreciate any help with this problem! Thanks again

Re: Speed - Another variable or continually refer to multidimensional array

Thanks for both your replies and advice.

And that timer array (I realise I need to change array(1,8) in the second loop to Var1!) will be a great help to lots of questions I have. Cheers

Hello,

I have a multidimensional array where I need to refer to one particular element thousands of times and was just wondering if there is any speed difference to have that element stored as a variable for ease of coding?

eg

dim array1(to 11,1 to 10) as long
dim Var1 as long


Var1 = array1(1,8)

Also, as I expect the final product to be quite large, I am making an effort to optimize the performance speed as much as I can and was wondering if there was anywhere I could read up on the different speeds of various functions, so I can choose the quickest one where options exist. I have read the "Optimize Slow VBA Code, Speeding Up Slow Excel VBA Code"

Thanks
[h=1][/h]

Re: Use value of a variable to specify another variable

I thought as much, thanks.

You are right, an array should make it easy enough.

Cheers

Hi,

Im not sure if this is possible, but was wondering if you could access a variable by using the current value of a different variable.

Hopefully this will make more sense of what I am trying to do by looking at this code, which I know is not how to do it - but hopefully makes more sense than my explanation!!!

Sub Test()


Dim Var1 as String
Dim Var2 as String
Dim Add_On as Long


Add_On = 1


Var & Add_On.Value = "Gerty"


End Sub

I am just trying to save myself from having to write a shed load of If commands within a project I am working on.

Thanks in advance

Re: Hyperlink to worksheet address

Thankyou very much, that works.

Sorry for inadvertantly posting the incorrect data to begin with

Re: Hyperlink to worksheet address

sorry

Re: Hyperlink to worksheet address

Ok, for simplicity I just used the term 'Player' for my sheet name in the example I gave above, when in actual fact all my worksheets have names such as 'Joe Bloggs', 'Delboy Lichte', etc.

It seems that the hyperlink wont work if there is a space in the variable/worksheet name.

I created another worksheet 'Player 2' and I get the same error as before with this code.

Sheets("Start Page").Select
Player_Name = "Player 2"
Player_Reference = "#" & Player_Name & "!a1"




ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("c21"), Address:="", SubAddress:=Player_Reference, TextToDisplay:=Player_Name

Re: Hyperlink to worksheet address

Ok, thats weird.

I created a new workbook to upload with the example data and it worked......
I'll double and triple check my code for a stupid error.

Re: Hyperlink to worksheet address

Thanks for the reply - i just tried that but still get same error message

Hello,

I'm using Excel 2003 with XP.

Ive been searching the net for hours for a solution and am stumped as to why this vba code wont work for me.

Sheets("Start Page").SelectPlayer_Name = "Player"
Player_Reference = PlayerName & "!a1!"
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("c21"), Address:="", SubAddress:=Player_Reference, TextToDisplay:=Player_Name

I've tried with several worksheets that definitely exist and spelled correctly ( I have even put sheets("player").select in first line of code to ensure that I am not doing something silly like misspelling the various sheetnames.)

For every one I have tried I always get a 'Reference is not valid' error

I would really appreciate any help you could give me!

Thanks in advance

Re: Count Of Each Name In Column

Thanks for your replies!

Id never used pivot tables before, but now see just how powerful a tool they can be. I'll have to make some effort to make myself more familiar with them.

Using the Data > subtotals was easier, but I couldnt work out how to move the totals into a seperate column and without disrupting the existing data.

Both are workable solutions, though - so i thank you both

Hi all!

In my spreadsheet column B represents a list of hundreds of names and what I would like to do is compile a list in column D which would represent each individual name that exists in column B and also the total number of times that name is lited in B.

For example :-

Column B could have the names
Bob
James
Bob
Phil
James
Dave
Bob

In this scenario columns D and E would output

Bob 3
James 2
Phil 1
Dave 1

The names in column B are not predefined and any name could exist.

I hope I have explained myself relatively clearly, and thank you for any help which is offered.

Cheers

Whenever I try to run a web query from a particular site, I get the following error message:-

Quote

File cannot be opened because:
Use of default namespace declaration attribute in DTD not supported.
Line 4, Position 7.
<html>

Can anyone tell me what this means, or better yet how I can get around it?

The webpage I am trying to access is http://www.twplus.org

Thanks in advance

Re: Countif Specific Value And In Range

Sorry, I didnt realise you had to have the cell selected when you press Ctrl/Shift/Enter. I get it now.
Cheers

Re: Countif Specific Value And In Range

Sorry, venkat actually i don't understand what you have done in cell F3.

How could I replicate that formula in another worksheet, with a different range?

Re: Countif Specific Value And In Range

Thanks Krish, However Im not very familiar with pivot tables.

Venkat, that is what I can understand - thankyou very much.

Hi guys, just wondering if anyone could please help me.

Column A stores player names, Column B stores the players score for that game.

A players name will appear numerous times in column A, as each different match will be posted, with his score for that match located in column B.

I would like to count the number of times each player scores between a certain range, in this situation between 50 and 100. Preferably I would like to use excel formulae, rather than VBA for this task.

I have attached a sample data sheet, which should show what I mean more clearly.

Cell D3 should represent the number of times "Beck" scored between 50 & 100
Cell E3 should represent the number of times "Beck" scored in excess of 100
Cell F3 should represent the highest score of "Beck"

I hope I have explained this ok.

Thanks in advance.