CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Inserting data into cell in different sheet (Excel & VBA)

I have a fully working code that does various fascinating things. One of the things it does is validating some stuff based on an admin table on the same sheet.

This admin table is to be moved to another sheet ("admin" sheet).

When I try to alter my code to do this at the other sheet, it gives me an error.

Example code when admin table is on the same sheet:

If LastRow = 12 Then
    Sheets("Project Name & Series").Range("U12").Formula = "=IF(COUNTIFS(TEPSD!A:A,F12,TEPSD!C:C,G12,TEPSD!E:E,H12)>=1,TRUE,FALSE)"
Else
Sheets("Project Name & Series").Range("U12").Formula = "=IF(COUNTIFS(TEPSD!A:A,F12,TEPSD!C:C,G12,TEPSD!E:E,H12)>=1,TRUE,FALSE)"
Sheets("Project Name & Series").Range("U12").AutoFill Destination:=Range("U12:U" & LastRow)
End If

Example of code adapted to do the same on another sheet:

If LastRow = 12 Then
    Sheets("Admin").Range("C12").Forumla = "=IF(COUNTIFS(TEPSD!A:A,'Project Name & Series'!F12,TEPSD!C:C,'Project Name & Series'G12,TEPSD!E:E,'Project Name & Series'H12)>=1,TRUE,FALSE)"
Else
Sheets("Admin").Range("C12").Forumla = "=IF(COUNTIFS(TEPSD!A:A,'Project Name & Series'!F12,TEPSD!C:C,'Project Name & Series'G12,TEPSD!E:E,'Project Name & Series'H12)>=1,TRUE,FALSE)"
Sheets("Admin").Range("C12").AutoFill Destination:=("C12:C" & LastRow)
End If

VBA does not like it. I think because the "admin" sheet is not activated?

What is the easiest way to alter my code to do this, without having to re-write most of it?

Thanks

Upvotes: 0

Views: 1799

Answers (1)

user4039065
user4039065

Reputation:

'Project Name & Series'G12 should be 'Project Name & Series'!G12

'Project Name & Series'H12 should be 'Project Name & Series'!H12

Forumla is spelled Formula

with workSheets("Admin")
    If LastRow = 12 Then
        .Range("C12").Formula = "=IF(COUNTIFS(TEPSD!A:A, 'Project Name & Series'!F12, TEPSD!C:C, 'Project Name & Series'!G12, TEPSD!E:E, 'Project Name & Series'!H12)>=1, TRUE, FALSE)"
    Else
        .Range("C12:C" & LastRow).Formula = "=IF(COUNTIFS(TEPSD!A:A, 'Project Name & Series'!F12, TEPSD!C:C, 'Project Name & Series'!G12, TEPSD!E:E, 'Project Name & Series'!H12)>=1, TRUE, FALSE)"
    End If
end with

Upvotes: 2

Related Questions