Posts by spog00

spog00 · Nov 28th 2005

Re: Adding a new Row

How about:

Code

Sub AddRow()


Sheets("Sheet1").Rows(20).EntireRow.Insert Shift:=xlDown


End Sub

Hope this helps

Alastair

spog00 · Oct 12th 2005

Re: Vlookup feature

Use Index/Match

See eg file..

Hope this helps

Alastair

spog00 · Oct 12th 2005

Re: Copy data

That is what the formula is doing...

Alastair

spog00 · Oct 12th 2005

Re: IF cell = "bla" change row colour

Check this out. This uses Conditional Formatting. If you need more than 3 colour options though, I would recommend VBA. Let me know...

Hope this helps

Alastair

spog00 · Oct 12th 2005

Re: Vlookup - multiple answers

Can you post an example spreadsheet please. It makes it much easier to picture what you are after!
Thanks,

Alastair

spog00 · Oct 12th 2005

Re: Copy data

Try this formula in the Answer rows:

=INDEX($B2:$B6,MATCH(1,C2:C6,0))

Then copy it across.

Hope this helps

Alastair

spog00 · Sep 16th 2005

Re: Delete Empyty Sheets

try this:

Code

Sub DeleteEmptySheets() 
    On Error Resume Next 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Dim ShNo As Byte 
    For ShNo = ActiveWorkbook.Sheets.Count To 1 Step = -1
        Sheets(Sh).Activate 
        If Application.WorksheetFunction.CountA _ 
        (ActiveWorkbook.Sheets(ShNo).Cells) = 0 Then 
        Sheets(ShNo).Delete 
    End If 
Next 
End Sub

Display More

Hope this helps

Alastair

spog00 · Sep 16th 2005

Re: Delete Empyty Sheets

Try this (untested)

Code

Sub DeleteEmptySheets() 
    On Error Resume Next 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Dim ShNo As Integer 
    For ShNo = 1 To ActiveWorkbook.Sheets.Count 
        Sheets(ShNo).Activate 
        If Application.WorksheetFunction.CountA _ 
        (ActiveWorkbook.Sheets(ShNo).Cells) = 0 Then 
        Sheets(ShNo).Delete 
    End If 
Next 
End Sub

Display More

Alastair

spog00 · Sep 16th 2005

Re: Disable Right Click selectionchange

It works beautifully! Thank you so much.

Alastair

spog00 · Sep 16th 2005

Re: create a sheet with a macro

Quote

On the date thing, instead of 1692005 can't it be Sept 16?

Code

Sub test()
     
    Dim shAddSheet As Worksheet
    Dim TodayDay As Integer
    Dim NewShName As String
     
    TodayDay = Application.WorksheetFunction.Weekday(Date, 1)
     
    Select Case TodayDay
    Case Is = 6: NewShName = Date + 7
    Case Is < 6: NewShName = Date + (6 - TodayDay)
    Case Is > 6: NewShName = Date - (TodayDay - 6) + 7
    End Select
     
    Select Case Month(NewShName)
    Case Is = 1: NewShName = "Jan " & Day(NewShName)
    Case Is = 2: NewShName = "Feb " & Day(NewShName)
    Case Is = 3: NewShName = "Mar " & Day(NewShName)
    Case Is = 4: NewShName = "Apr " & Day(NewShName)
    Case Is = 5: NewShName = "May " & Day(NewShName)
    Case Is = 6: NewShName = "June " & Day(NewShName)
    Case Is = 7: NewShName = "July " & Day(NewShName)
    Case Is = 8: NewShName = "Aug " & Day(NewShName)
    Case Is = 9: NewShName = "Sept " & Day(NewShName)
    Case Is = 10: NewShName = "Oct " & Day(NewShName)
    Case Is = 11: NewShName = "Nov " & Day(NewShName)
    Case Is = 12: NewShName = "Dec " & Day(NewShName)
    End Select
         
    Set shAddSheet = Sheets.Add
    shAddSheet.Name = NewShName
     
End Sub

Display More

Hope this helps

Alastair

spog00 · Sep 16th 2005

Re: Increment a For Loop

Code

Sub Test() 
     
     'Written by Alastair Stringfellow
     'For [url]www.Ozgrid.com[/url]
    
Dim i, CountTo As Integer
 
CountTo = Range("B3").Value


    For i = 1 To CountTo 
         
        Sheets("Sheet1").Range("A" & i+1).Value = i 
    Next 
     
End Sub

Display More

Alastair

spog00 · Sep 16th 2005

Re: Disable Right Click selectionchange

Cheers guys, this is excellent stuff!! Hadn't expected so many replies. Will test them and see... Unfortunately as has been pointed out, the selection change is activated before the right-click...But using OnTime is a stroke of genius! Will check it and see....

Barry: Good thinking about the transparent textbox as well, I like it, however in this case, I have potentially hundreds of cells that are within the Right-click-able range, and the range is variable, so I fear using textboxes could get messy, thanks anyway

Thanks,

Alastair

spog00 · Sep 16th 2005

Re: A newbee

Code

Sub Test()


'Written by Alastair Stringfellow
'For [url]www.Ozgrid.com[/url]


For i = 1 to 10


Sheets("Sheet1").Range("A" & i+1).Value = i
Next


End Sub

Display More

Alastair

PS, for future reference please use meaningful titles, or you thread will get locked

spog00 · Sep 16th 2005

Re: Range Reference

This really does work now... Not sure what was wrong earlier though...

Code

Private Sub Worksheet_Activate()
    Dim i As Integer 
Dim maxcell,rng As Range
    Dim maxVal As Double
     
    For i = 1 To Sheets("Weekly Matches").Range("E1").Value
        With Sheets("Weekly Matches")
            Set Rng = .Range(.Cells(11 * i, 2), .Cells(11 * i, 22))
        End With
            
        maxVal = WorksheetFunction.Max(Rng)
        With Rng
            Set maxcell = .Find(maxVal, LookIn:=xlValues, lookat:=xlWhole)
        End With
        Sheets("Standings").Cells(28 + i, 4) = maxcell.Value
        Sheets("Standings").Cells(28 + i, 3) = maxcell.Offset(-8, 0).Value
    Next i
    
End Sub

Display More

Hope this helps

Alastair

spog00 · Sep 16th 2005

Re: Cell color formatting

Quote

I think i need to amend the select case to a nested if loop alongwith a while loop

Shouldn't be necessary, and is much more complicated than it could be....

Can you post an example? - Just as easy to stick to one thread...also gets frustrating and confusing if there are too many threads with the same subject

Alastair

spog00 · Sep 16th 2005

Re: macro to paste special value or format

There is also a Paste Values toolbar button you can get...

Right Click on a toolbar, then select Customise, select the Commands tab, then Select Edit from the menu on the left. Scroll down, and find the PasteValues button, then drag to the relevant place on your toolbar.

Hope this helps

Alastair

spog00 · Sep 16th 2005

Re: Range Reference

Strange, this worked fine for me...
Can you post an example sheet?

Alastair

spog00 · Sep 16th 2005

Re: Add sheets Macro - check duplicate name

I think this has already been covered but in a slightly different way:

Code

Sub test()


On Error GoTo AddNewSheet
Sheets("SheetNameToCheck").Select
On Error GoTo 0


'Your code


Exit Sub


AddNewSheet:
Set shNewSheet = Sheets.Add
shNewSheet.Name = "SheetNameToCheck"
Resume


End Sub

Display More

Hope this helps

Alastair

PS, Localan's code will work fine, but may be slow in a very big workbook...though it would have to be seriously large to notice anything I have used something similar on workbooks with 300 sheets, and noticed nothing - admittedly they were empty sheets....Make your own mind up

spog00 · Sep 16th 2005

Re: Range Reference

Ok, try a different tack:

Code

Private Sub Worksheet_Activate()
    Dim i As Integer, maxcell As Range
    Dim maxVal  As Double
    
    For i = 1 To Sheets("Weekly Matches").Range("E1").Value
        Sheets("Weekly Matches").Activate
        Range(Cells(11 * i, 2), Cells(11 * i, 22)).Select
        maxVal = WorksheetFunction.Max(Selection)
        Set maxcell = Selection.Find(maxVal)
        Sheets("Standings").Cells(28 + i, 4) = maxcell.Value
        Sheets("Standings").Cells(28 + i, 3) = maxcell.Offset(-8, 0).Value
    Next i
End Sub

Display More

Hope this helps

Alastair

spog00 · Sep 15th 2005

Re: Range Reference

See if this works:

Code

Set maxcell = [COLOR=Red]Application.[/COLOR]WorksheetFunction.Max(Range(Cells(i * 11, 1), Cells(i * 11, 22)))

Hope this helps

Alastair