IF macro VBA

[Robert72]

Hi,

i want to make a macro with a if formula that takes a value and placed it on the right row every row got's a specific date.

this is what i got so far:

If Sheets("Data").Range("A2") = "1" Then Sheets("Numpay").Range("F312").Copy
Sheets("3C").Select
Range("D12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'other date:
If Sheets("Data").Range("A2") = 2 Then Sheets("Numpay").Range("F312").Copy
Sheets("3C").Select
Range("F12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'the sheet data range A2 = the day
'in the sheet numpay are placed the values.
'sheets 3c is the sheet where it needs to be placed.

im working in excel 2003
greetz

 

[Robert72]

in the sheet 3C row 4.

in the sheet data is also put a cell. (D3) it looks like: 01-11-2011
some other values you maybe need
A2 = day (DD)
B2 = Month (MM)
C2 = Year (YYYY)

the cells in the sheet data are automatically filled in when you fill in a date in the userform i made. so that dates are all variabel.

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim c As Long
    c = WorksheetFunction.Match(CLng(Worksheets("Data").Range("D3").Value), Worksheets("3C").Rows(4), False)
    Worksheets("3C").Cells(12, c).Value = Worksheets("Numpay").Range("F312")
End Sub

 

[Robert72]

it's working!

i want to do this also by a other sheet (in the same file)
the sheet called Hypercom

de date in the sheet hypercom is in row 4. the same as in the sheet 3C.
the first date is located in cell D4

this is what i got so far. but i get code "1004"

c = WorksheetFunction.Match(CLng(Worksheets("Data").Range("D3").Value), Worksheets("Hypercom").Rows(4), False)
Worksheets("Hypercom").Cells(19, A).Value = Worksheets("Numpay").Range("F311")

what is incorrect?

 

[Andrew Poulsom]

What's A? Should it be?

Worksheets("Hypercom").Cells(19, c).Value = Worksheets("Numpay").Range("F311")

 

[Robert72]

that isn't the problem.
the bug marks the first row yellow,
c = WorksheetFunction.Match(CLng(Worksheets("Data").Range("D3").Value), Worksheets("Hypercom").Rows(4), False)

i don't know whats wrong with this row???

bye

 

[Andrew Poulsom]

Are you sure that the date is in row 4 on worksheet Hypercom? What is the text of the error?

 

[Robert72]

yes the dates are in row 4

the error:

error 1004 during execution
Feature match of class worksheet functions can not be retrieved

de 1st date 1-11-2011 is located at cell D4 the 2nd (2-11-2011) at cell F4

between de dates is a empty collum placed. that's also in the 3C sheet.

 

[Andrew Poulsom]

Sorry, I don't know why the code works on one sheet but not another, unless your dates aren't serial dates but text entries that look like dates.

 

[Robert72]

i solved! the dates in the hypercom sheet hasn't got formulas to the date in sheet data.

thanks for your help.

kind regards