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

    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