Solved: Solved:

[dani9]

Hi guys.

can someone help me with the formula below:


For lngCol = 1 To lngColMax

If WorksheetFunction.CountIf(Sheets("Settings").Range("D12:D21"), Sheets(CStr(lngTabelle)).Cells(1, lngCol)) <> 0 Then
Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).FormulaR1C1 = "=IF(ISERROR(YEAR('" & lngTabelle & "'!R[0]C[0])),'" & lngTabelle & "'!R[0]C[0],IF(YEAR('" & lngTabelle & "'!R[0]C[0])=YEAR(NOW()),CONCATENATE(DAY('" & lngTabelle & "'!R[0]C[0]),""."",MONTH('" & lngTabelle & "'!R[0]C[0])),CONCATENATE(MONTH('" & lngTabelle & "'!R[0]C[0]),""."",RIGHT(YEAR('" & lngTabelle & "'!R[0]C[0]),2))))"
Else
Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).FormulaR1C1 = "=IF(R1C<>0,INDEX('" & lngTabelle & "'!R2C1:R" & lngRowMax & "C" & lngColRaw & ",,R2C[182]),"""")"
End If

Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).AutoFill Destination:=Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)), Type:=xlFillDefault
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).Copy
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).PasteSpecial xlPasteValuesAndNumberFormats

it doesnt seem to recognize the CountIf, it should compare the data and if it matches, the formula should me used, but instead it just uses the second formula from Else. What went wrong???

[Bob Phillips]

This worked for me



If WorksheetFunction.CountIf(Sheets("Settings").Range("D12:D21"), Sheets(lngTabelle).Cells(1, lngCol)) <> 0 Then
cell.FormulaR1C1 = "=IF(ISERROR(YEAR('" & lngTabelle & "'!R[0]C[0])),'" & lngTabelle & "'!R[0]C[0],IF(YEAR('" & lngTabelle & "'!R[0]C[0])=YEAR(NOW()),CONCATENATE(DAY('" & lngTabelle & "'!R[0]C[0]),""."",MONTH('" & lngTabelle & "'!R[0]C[0])),CONCATENATE(MONTH('" & lngTabelle & "'!R[0]C[0]),""."",RIGHT(YEAR('" & lngTabelle & "'!R[0]C[0]),2))))"
Else
cell.FormulaR1C1 = "=IF(R1C<>0,INDEX('" & lngTabelle & "'!R2C1:R" & lngRowMax & "C" & lngColRaw & ",,R2C[182]),"""")"
End If

cell.AutoFill Destination:=Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)), Type:=xlFillDefault
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).Copy
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).PasteSpecial xlPasteValuesAndNumberFormats


but as I have no idea of its context or the values of all of those variables, who knows?

[dani9]

it recognizes the formula but in the wrong column. If i illustrate it:
I have raw data and from this raw data the VBA should copy paste into Sheet F1, but the problem is, that the column 36 in raw data should be copied in column 23 in Sheet F1, and it doesnt.

It works for the other columns, but when it comes to column 36, it copies it into F1 - column 36, which is wrong. he doesn´t match it, whilst he matches is for the other ElseIF.
So how can i tell him to put it into the right column???

I don´t know if i explained it right... if it is easier, i can put up a part of the file?

[Bob Phillips]

You said it was the countif that wasn't working, now you are saying it is the copy/paste.

Regardless, I am working blind here, I have far too little to work with.

[dani9]

i am sorry, but i tend to figure aout another problems when i solve one. So, i am basically trying to fix the formula after another person and it is hard, especially because im new in VB.

So please have patience :)

Maybe my problem would be solved if i somehow put the INDEX from the second formula into the first formula (with ISERROR). but i don´t know how to do it? Can someone help me with that? I would really appreciate it!

Please please please

[Aussiebear]

Please post a sample workbook based on the same layout so we can better understand your issue. To past a workbook, click on "Go Advanced", scroll down to Manage Attachments and follow through from there.

[Bob Phillips]

So please have patience :)

My patience is almost inestimable, but it won't achieve anything unless you give us more information, and preferably a sample workbook.

[dani9]

Hi,

here is a sample of the work book, with macros, amd in the segment Dataselection of F is the pasted code. If you take a look at the sheet 1 you will see, that in columns AJ, AK, AL, and AP i have the values but in some cells there are dates, now these values have to be inserted into sheet F1 in columns W, X and Y with formating the dates back into the numbers, which is taken care of the formula. but what i need is to INDEX it so it goes into the right columns (like it is done in the ELSE part of the If statement).

Hope you understand it now.

I hope we can solve this :banghead: :banghead:

[halimi1306]

hi,

what if the source sheet is more than one and need to fit the main wb. Let say the source sheet have a different no of row and the source sheet might have blank row and need to skip that blank.
FYI, im now working with 85 source sheets and need to import those data in my wb. you could have a look at my sheet in my post earlier

[dani9]

Good news, i saved the problem myself :)

just indexed one column after another, it was not a lot of work, so it was ok!

thanks anyway!