Fill Down Function

[Nomas]

Good Morning!

I am trying to run this to full a calculation down a sheet ( to keep file size small) I am running into errors on the lines indicated below by arrows to the right, any guidance is appreciated!



Sub FillCalc_down()

' FillCalc_down
'

'
Sheets("Item Upload Template").Select
Sheets ("Item Upload Template") <----------
Range("A3:BY3").Select
Application.CutCopyMode = False
*** Selection.AutoFill Destination:=Range("A3:BY12000") <-----------


End Sub

 

[Nomas]

Basically I am looking to compare all the values in a given row on 2 different sheets, and get a true/false answer if there are any that are different

so


Sheet1 Row 1

Sheet2 Row 1

If Sheet 1 Row 1 Column J has a 2 in it and
if Sheet 2 Row 1 Column J has a A in it

(or a 1 for that matter, anything but a 2) I want it to return FALSE

 

[bjurney]

This will post Upload if there is a 2 in Row 1 or Column J on sheet1 AND an A on Row 1 or Column J on sheet2

=IF(OR(NOT(OR(COUNTIF(Sheet1!1:1,"2"),COUNTIF(Sheet1!J:J,"2"))),NOT(OR(COUNTIF(Sheet2!1:1,"A"),(COUNTIF(Sheet2!J:J,"A"))))),"No Change","Upload")

I believe this is what you were asking for. I hope I am understanding correctly.

 

[Nomas]

I'm afraid I may not be skilled enough to explain it correctly

Hopefully this helps


Sheet 1
A B C D E F G
1 1 A TRUE 2 100 4 0.1
2 5 A TRUE 3 100 4 0.4
3 12 A TRUE 2 44 4 0.1
4 5 A FALSE 2 100 4 0.6
5 10 A TRUE 2 100 4 0.1
6 3 A TRUE 7 26 4 0.1
7 88 A FALSE 2 100 4 0.5
8 41 A TRUE 2 100 4 0.1

Sheet 2
A B C D E F G
1 1 A TRUE 2 100 4 0.1
2 5 A TRUE 3 100 4 0.4
3 12 A TRUE 2 56 4 0.1
4 5 A FALSE 2 100 4 0.6
5 10 B TRUE 2 100 4 0.1
6 3 A TRUE 7 26 4 0.1
7 88 A FALSE 2 100 4 0.8
8 22 A TRUE 2 100 4 0.1


Sheet 3
A
1 No Change
2 No Change
3 Upload
4 No Change
5 Upload
6 No Change
7 Upload
8 Upload


I need a formula that will diff sheet 1 and 2 by row looking at all values and return No Change if they equal or Upload if one of the values in the whole row is changed. Hope this clears it up. I appreciate all the help so far. You have been great!

 

[bjurney]

You were along the right lines with your concatenate formula, just need to break it up by cell instead of doing the entire row. Something like this shold do the trick, you would need to put each cell value in though if it goes past column G and you are limited to 30 cells if you are using 2003 or older. which you would have to nest the concatenate formula.

Try this though, see where that gets you:

=IF(EXACT(CONCATENATE(A1,B1,C1,D1,E1,F1,G1),CONCATENATE(Sheet2!A1,Sheet2!B1,Sheet2!C1,Sheet2!D1,Sheet2!E1,Sheet2!F1,Sheet2!G1)),"No Change","Upload")

 

[Nomas]

OK the concatenate works great while small (only comparing a few cells) but eventually the population of the massive concatenate turns the whole code red and takes up several lines. I can paste in if necessary but dont want to make your eyes bleed from the wall of text it creates. Its basically turned the formula lines red, everything else still sees valid. If you want me to post the text I can. Also I am using 2007 and this is saved as a 2007 macro enabled workbook.

 

[Nomas]

Here is the code for reference. I know its ugly, any guidance on how to clean up is appreciated.

Sheets("Item Upload Template").Range("A3").Formula =
IF(CONCATENATE('Item List'!A2,'Item List'!B2,'Item List'!C2,'Item List'!D2,'Item List'!E2,'Item List'!F2,'Item List'!G2,'Item List'!H2,'Item List'!I2,'Item List'!J2,'Item List'!K2,'Item List'!L2,'Item List'!M2,'Item List'!N2,'Item List'!O2,'Item List'!P2,'Item List'!Q2,'Item List'!R2,'Item List'!S2,'Item List'!T2,'Item List'!U2,'Item List'!V2,'Item List'!W2,'Item List'!X2,'Item List'!Y2,'Item List'!Z2,'Item List'!AA2,'Item List'!AB2,'Item List'!AC2,'Item List'!AD2,'Item List'!AE2,'Item List'!AF2,'Item List'!AG2,'Item List'!AH2,'Item List'!AI2,'Item List'!AJ2,'Item List'!AK2,'Item List'!AL2,'Item List'!AM2,'Item List'!AN2,'Item List'!AO2,'Item List'!AP2,'Item List'!AQ2,'Item List'!AR2,'Item List'!AS2,'Item List'!AT2,'Item List'!AU2,'Item List'!AV2,'Item List'!AW2,'Item List'!AX2,'Item List'!AY2,'Item List'!AZ2,'Item List'!BA2,'Item List'!BB2,'Item List'!BC2,'Item List'!BD2,'Item List'!BE2,'Item List'!BF2,'Item List'!BG2,'Item List'!BH2,'Item List'!BI2,'Item List'!BJ2,'Item List'!BK2,'Item List'!BL2,'Item List
'!BM2,'Item List'!BN2,'Item List'!BO2,'Item List'!BP2,'Item List'!BQ2,'Item List'!BR2,'Item List'!BS2,'Item List'!BT2,'Item List'!BU2,'Item List'!BV2,'Item List'!BW2,'Item List'!BX2,'Item List'!BY2)=CONCATENATE('Item List Checksheet'!A2,'Item List Checksheet'!B2,'Item List Checksheet'!C2,'Item List Checksheet'!D2,'Item List Checksheet'!E2,'Item List Checksheet'!F2,'Item List Checksheet'!G2,'Item List Checksheet'!H2,'Item List Checksheet'!I2,'Item List Checksheet'!J2,'Item List Checksheet'!K2,'Item List Checksheet'!L2,'Item List Checksheet'!M2,'Item List Checksheet'!N2,'Item List Checksheet'!O2,'Item List Checksheet'!P2,'Item List Checksheet'!Q2,'Item List Checksheet'!R2,'Item List Checksheet'!S2,'Item List Checksheet'!T2,'Item List Checksheet'!U2,'Item List Checksheet'!V2,'Item List Checksheet'!W2,'Item List Checksheet'!X2,'Item List Checksheet'!Y2,'Item List Checksheet'!Z2,'Item List Checksheet'!AA2,'Item List Checksheet'!AB2,'Item List Checksheet'!AC2,'Item List Checksheet'!AD2,'Item List Checksheet'!AE2,'
Item List Checksheet'!AF2,'Item List Checksheet'!AG2,'Item List Checksheet'!AH2,'Item List Checksheet'!AI2,'Item List Checksheet'!AJ2,'Item List Checksheet'!AK2,'Item List Checksheet'!AL2,'Item List Checksheet'!AM2,'Item List Checksheet'!AN2,'Item List Checksheet'!AO2,'Item List Checksheet'!AP2,'Item List Checksheet'!AQ2,'Item List Checksheet'!AR2,'Item List Checksheet'!AS2,'Item List Checksheet'!AT2,'Item List Checksheet'!AU2,'Item List Checksheet'!AV2,'Item List Checksheet'!AW2,'Item List Checksheet'!AX2,'Item List Checksheet'!AY2,'Item List Checksheet'!AZ2,'Item List Checksheet'!BA2,'Item List Checksheet'!BB2,'Item List Checksheet'!BC2,'Item List Checksheet'!BD2,'Item List Checksheet'!BE2,'Item List Checksheet'!BF2,'Item List Checksheet'!BG2,'Item List Checksheet'!BH2,'Item List Checksheet'!BI2,'Item List Checksheet'!BJ2,'Item List Checksheet'!BK2,'Item List Checksheet'!BL2,'Item List Checksheet'!BM2,'Item List Checksheet'!BN2,'Item List Checksheet'!BO2,'Item List Checksheet'!BP2,'Item List Checksheet'!BQ
2,'Item List Checksheet'!BR2,'Item List Checksheet'!BS2,'Item List Checksheet'!BT2,'Item List Checksheet'!BU2,'Item List Checksheet'!BV2,'Item List Checksheet'!BW2,'Item List Checksheet'!BX2,'Item List Checksheet'!BY2)
,""No Change"",""Upload"")"


Thanks