1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

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

  1. sirdragon

    sirdragon Nibble Poster

    70
    0
    11
    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.




    Code:
     [font=Courier]
    
     
     
     
    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 
    		.Delete 
    		.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ 
    		:=xlBetween 
    		.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 
    	Selection.Copy 
    		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 
    [/font]


    [​IMG]



    any help greatly appreciated.....

    cheers martin
     
  2. Phil
    Honorary Member

    Phil Gigabyte Poster

    1,680
    7
    87
    Sorry Martin,

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

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

    sirdragon Nibble Poster

    70
    0
    11
    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

    7,796
    71
    224
    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

    70
    0
    11
    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

    #code
    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....

    cheers

    martin
     

Share This Page

Loading...