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

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.
2) Use x as the col offset position [this gets an 1004 error]
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]
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]

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]

​

any help greatly appreciated.....

cheers martin

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Sorry Martin,

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

Phil

 

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

 

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.

 

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