problems with code syntax [i]HELP[/i] please

Discussion in 'Microsoft Office Specialist (MOS), Office 365' started by sirdragon, Jun 12, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    Hi Guys

    maybe you can help me out with this one. i have been looking at it for hours and i KNOW there is a simple solution but i have no idea what it is.

    this code is supposed to
    1) increase c by 2 after each run through. [​IMG]
    2) Use x as the col offset position [this gets an 1004 error] [​IMG]
    3) select autofilter field (this is conditionally formatted)and copy paste special everything then, get rid of all except the format from the conditional formatting. [think this should work] [​IMG]
    4) checks if cell has a format if it does value = OT
    5) finally pastes in the names from "namerge" according to the criteria field. In the column before the OT column.[this worked before i added the rest of the code lol] [​IMG]

    the code does this 28 times ie 4weeks.

    Sub SelectE() 
    Dim i As Integer 
    Dim c As Integer 
    Dim x As Integer 
    x = 1 
    c = 0 
    For i = 3 To 31 'daily counter 
    	c = c + 2 'increment c by 2 
    	x = c + 1 'if c= 2 x=3 etc 
    If Application.WorksheetFunction.CountIf(Sheets("sheet1").Range(Cells(1, i), Cells(42, i)), "E") >= 1 Then 
    	Selection.AutoFilter Field:=i, Criteria1:="E" 
    'paste to get any formatting 
    Selection.Copy 'hopefully this will select autofilter field:=3 first time around 
    	Range(ak11).Offset(RowOffset:=0, ColumnOffSet:=x).Select 'hopefully puts OT in col after namerge 
    'runtime error 1004 method range of global object failed 
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
    		False, Transpose:=False 
    	Application.CutCopyMode = False 
    	With Selection.Validation 'delete anything but formatting 
    		.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ 
    		.IgnoreBlank = True 
    		.InCellDropdown = True 
    		.ShowInput = True 
    		.ShowError = True 
    	End With 
    	Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    	Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    	Selection.Borders(xlEdgeLeft).LineStyle = xlNone 
    	Selection.Borders(xlEdgeTop).LineStyle = xlNone 
    	Selection.Borders(xlEdgeBottom).LineStyle = xlNone 
    	Selection.Borders(xlEdgeRight).LineStyle = xlNone 
    	Selection.Borders(xlInsideVertical).LineStyle = xlNone 
    	Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 
    If Cells.Select.Interior.ColorIndex < 0 Then 'not sure of syntax 
    cell.Value = "OT"	'put "OT" in selected cell if it contains formatting 
    End If 
    	Range("namerge").Select 'puts names in column next to "OT" column 
    		If c > 1 Then 
    	 Range(ak11).Offset(RowOffset:=0, ColumnOffSet:=c).Select 'c needs to increment by 2 each run through 
    		End If 
    	Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    	 :=False, Transpose:=False 
    	 Selection.AutoFilter Field:=i 'put autofilter back for next column selection 
    	End If 
    Next 'repeat all for next column c increased by 2 i by 1 
    End Sub 


    any help greatly appreciated.....

    cheers martin
  2. Phil
    Honorary Member

    Phil Gigabyte Poster

    Sorry Martin,

    I've had a look over it but can't see anything.

    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  3. sirdragon

    sirdragon Nibble Poster

    do you know what this means

    'runtime error 1004 method range of global object failed

    at least that would give me a starting point ...

    cheers martin
  4. nugget
    Honorary Member

    nugget Junior toady

    Not too sure but where is this Range (ak11) defined?

    Here's a link to something that might make sense to you.

    Actually here's a better one.
    Certifications: A+ | Network+ | Security+ | MCP (270,271,272,290,620) | MCDST | MCTS:Vista
    WIP: MCSA, 70-622,680,685
  5. sirdragon

    sirdragon Nibble Poster

    well done you,,,

    knew it was something simple for the first bit of the problem
    that should have been ("ak11") not (ak11) ___----> idiot I lol.
    now it progresses to

    If Cells.Select.Interior.ColorIndex < 0 Then

    with an error 424 object required

    so the syntax for that is all wrong as i thought.

    now I gotta try and find the correct object so that it refers to the cells just pasted. I suppose another IF statement might work...

    many thanks Nugget....



Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.