Posts by billybaxter

billybaxter · Nov 9th 2006

Re: Transfer Data From One Sheet To Another

Simon
I hadn't checked all of the named ranges - I've done so now and found that one had a hashREF - so all rectified now and working correctly - thanks for this - thanks to you and Georg for your time
regards
Billy

billybaxter · Nov 9th 2006

Re: Transfer Data From One Sheet To Another

Georg
Thanks for your reply, but the range which this refers to is still there - still valid from what I can see.
The file is far too large to submit here (1Mb +) and I wouldn't knoew which bits to copy and paste as an example in case I missed some.
As for deleting - yes - rows of information are deleted and added all the time - usually without any problem butI have recently deleted a top row of information which might have had something to do with it??
regards
Billy B

billybaxter · Nov 9th 2006

Hi
I have a button on one sheet of my file which should, when clicked, transfer the data in two separate columns to a particular column on another sheet.
Until recently this has worked without problem - now however whenever the button is clicked nothing at all happens - this code was written for me, I don't know VBA at all. I attach the code here firstly to see if anyone can see that the problem may exist within it, although there do not appear to be any error messages or highlights:

Code

Private Sub cmdTournament_Click()


    Dim s As Range, i As Variant, t As Range, w As Range, _
        cc As Range, c As Range, w2 As Range, j As Integer, Val As Integer
    Val = Sheet3.Range("Tournament").Value
    Set s = Sheet4.Range("Scores")
    Set t = Sheet3.Range("Table")
    Set w = t.Cells(1, 1).End(xlToRight).Offset(0, 1) 'first empty week
    Set w2 = t.Cells(1, 1).Offset(0, 31).End(xlToRight).Offset(0, Val)
   
    Application.Calculation = xlManual 'turn off calculations
    
    For Each cc In s
        For j = -1 To 4 Step 5
        Set c = cc.Offset(0, j)
        i = Application.Match(c.Offset(0, -3), t, 0) 'get player position
        If IsError(i) Then
            MsgBox "Cannot locate " & c.Offset(0, -3) & " in the table."
        Else
            w2.Cells(i, 1).Value = c
        End If
        Next j
    Next cc
       w2.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False
    Application.Calculation = xlAutomatic 'turn on calculations


End Sub

Display More

thanks in advance for any help
Billy B

billybaxter · Nov 8th 2006

Re: Method 'Range' of object 'Global' failed

Dave
A4 it is! and I know (think) the reason now is that although players join and leave and so get added and deleted all the time - I have only just taken the player off who was at the top of the list and has been since the start (A4)
It now once again works fine so once again thanks for your expert help
regards
Billy B

billybaxter · Nov 8th 2006

Re: Method 'Range' of object 'Global' failed

Dave
OK! - "Table" is defined as - =OFFSET(Players!#REF!,0,0,COUNTA(Players!$A$4:$A$567),1)
This (A4:A567) is a list of players names - so would the first players name be what should go in where the REF! is above?
Billy

billybaxter · Nov 8th 2006

Re: Method 'Range' of object 'Global' failed

Thanks for your reply Dave but I don't know what to do about this - the code relates to a squash ranking list (which you have helped with in the past). Specifically it takes points allocated to each player on one sheet and transfers those points into the next available column (the next week), next to that player's name on another sheet where each week's scores are then summed.
If the range which now no longer exists is the list of players and their points which need to be transferred then how shall I get this to function again as the list does still exist; nothing has been deleted - except - ass always players join and leave the league - and so those leaving are deleted, but this is not new.
Is this any help in describing what I might be able to do?
I think you could probably easily solve this if you saw the file but it is far too big to attahc here.
here's hoping
Billy B

billybaxter · Nov 8th 2006

Re: Run-time Error 1004

Sorry perhaps i should have mentioned - the problem line of code seems to be the 6th line

Code

Set t = Range("Table")

as that line is highlighted in yellow
cheers
Billy B

billybaxter · Nov 8th 2006

I have a button on my spreadsheet which activates some code, it has worked perfectly for over a year but now for some reason I cannot get it to work.
When I click on the button now I get the message: Run-time error '1004' Method 'Range' of object '_Global' failed.
Apologies if this is a simply query but i do not understand VBA at all, the file is far too large to attach here but here is the relevant code:

Code

Sub UpDateResults()
    Dim s As Range, i As Variant, t As Range, w As Range, _
        cc As Range, c As Range, w2 As Range, j As Integer, Val As Integer
    Val = Sheet3.Range("AG1").Value
    Set s = Range("Scores")
    Set t = Range("Table")
    Set w = t.Cells(1, 1).End(xlToRight).Offset(0, 1) 'first empty week
    Set w2 = t.Cells(1, 1).Offset(0, 31).End(xlToRight).Offset(0, Val)
   
    Application.Calculation = xlManual 'turn off calculations
    If Sheet4.Range("IV1").Value = 1 Then
    For Each cc In s
        For j = -1 To 4 Step 5
        Set c = cc.Offset(0, j)
        i = Application.Match(c.Offset(0, -3), t, 0) 'get player position
        If IsError(i) Then
            MsgBox "Cannot locate " & c.Offset(0, -3) & " in the table."
        Else
            w2.Cells(i, 1).Value = c
        End If
        Next j
    Next cc
       w2.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False


    Else
    For Each cc In s
        For j = -1 To 4 Step 5
        Set c = cc.Offset(0, j)
        i = Application.Match(c.Offset(0, -3), t, 0) 'get player position
        If IsError(i) Then
            MsgBox "Cannot locate " & c.Offset(0, -3) & " in the table."
        Else
            w.Cells(i, 1).Value = c
        End If
        Next j
    Next cc
       w.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False


    End If


   'w.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _
        'SearchOrder:=xlByRows, MatchCase:=False
    Application.Calculation = xlAutomatic 'turn on calculations
Sheet4.Range("IV1").Value = 0


End Sub

Display More

As usual any help would be greatly appreciated
regards
Billy B

billybaxter · Apr 29th 2006

Re: Print Table exact size

I have attached a file here as an example, maybe somebody can look at this and see what, if anything, I am doing wrong. I have tried 2 separate printers with the same result - a table which is about 9.5cm square rather than 10cm square as I would like.
I need it to be exact as I want to use the grid as a blank map - and this would equate to a blank 1:50,000 which I can then use for a navigation exercise where grid references and bearings can be measured exactly (if you understand that) - anyway it doesn't work properly if the table is not exact.
I have done it before and it worked without any problem - so is it to do with my set up of the table??
thanks
Billy B

billybaxter · Apr 29th 2006

I have set a simple tabel 5 x 5 cells to print at 2cm square per cell so that the table overall is exactly 10cm square. It looks like it is on the computer screen but when it prints, it is slighty smaller than I want.
Any help as usual much appreciated
thanks
Billy B

billybaxter · Apr 26th 2006

Re: Autofilter more than one list concurrently

Thanks Ger
They don't need to be side by side but the actual lists are very long and it far easier to read and print the lists side by side rather than underneath each other.
I'm still trying - but help required thanks
Billy

billybaxter · Apr 26th 2006

Here is a simple worksheet with 3 separate tables side by side which I would like to filter. when autofilter is applied to one or more ans I filter on just onwe list the filter is applied all lists at the same time which causes some information which I want to see to be hidden.
I want to filter out any information which shows a "0" next to it, without hiding information beside it in another table. Is there a way to do this?
As usual any help is much appreciated
regards
Billy B

billybaxter · Apr 24th 2006