Go Back

Range contains a value not in another range

Check if Range Contains a Value Not in another Range

Generic Formula

=SUMPRODUCT (--(ISNA(MATCH(rngA,rngB,0))))>0

Explanation

Excel uses a formula to find if a range contains, one or more values, are not in the other defined range. For this purpose, you can use SUMPRODUCT along with MATCH and ISNA.

Example

For your reference, please check the image. The formula in G6 is:

=SUMPRODUCT(--(ISNA(MATCH(alist,blist,0))))>0

Figure 1. Example to check if range contains a value that not in another range

How This Formula Works

Usually, the MATCH function searches for a single value and returns the same if found. While in the current example, we have given an array to the MATCH function so we’ll receive an array of results. We have configured the MATCH function to find the exact match, so, in case, if it is not found, we’ll receive the #N/A error from the MATCH function. We’ll observe the following results after the MATCH runs.

=SUMPRODUCT(--(ISNA({3;5;6;2;#N/A;4})))>0

You can clearly see “#NA” error in the above product. Thus, to inspect #N/A errors, we have used ISNA function. Though, the error may not occur always.

ISNA gives the following results:

=SUMPRODUCT(--({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))>

Further, the conversion of TRUE FALSE values to ones and zeros is done with the help of double negative operator (double unary), which results in the following:

=SUMPRODUCT({0;0;0;0;1;0})>0

In the end, the elements are summed by the SUMPRODUCT in the array, and we receive a TRUE or FALSE result after it is compared to zero. This is how this formula works to check if range contains values, not in another range.

Note

This formula is a combination of three different functions, to give you the perfect result, when you are in need of sorting the excel files with a high amount of data.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

As below this is my project in Excel VBA.Not sure whether as to why the other parameters are wrong when you start the macros especially in Dispalcement,TPC ,LCF ang MTC other parameters are all ok. Please advise.Thanks '' ' ' ---------------------------------------------------------------------------------------------------------------- ' ' <<< Module # 1 >>> ' ' ---------------------------------------------------------------------------------------------------------------- ' ' ' Macros for the database switches within the standard program ' ' Sub ListBoxChange() ' ' ' Macro is designed to insert Vessel information from the Internal Database ' ' Pre-set vessel information is stored within an internal databases located within the program. This data consist ' of vessel name, POR,GRT, NETT,S.DRAFT,S.DISPL,S.DWT,LIGHTSHIP,LBP,BEAM,LOA,FWA,CARGO HOLD LCG,INITIAL and FINAL DRAFT PP DISTANCES,CARGO HOLD CAPACITY,DISPLACEMENT,TPC.LCF,MTC ' profile. As each vessel name is selected, the corresponding data for that vessel is up-loaded throughout the ' program. Database confirmation statements will indicate if the relevent data stored within is verified accurate ' or not. ' Location - Data Entry Page ' ' ' Section I - Test the Vessel Listbox for change in value ' ' ' Test the Vessel Counter Listbox for change in value If Sheet1.Range("BI7").Value = Sheet7.Range("B5").Value Then Exit Sub ' Change occured Else Sheet7.Range("B5").Value = Sheet1.Range("BI7").Value ' Change occurred End If ' ' ' Section II - Up-load the stored data to the program when selected ship name in list box to change all parameters ' ' ' Sheet1.Range("A11").Value = Sheet7.Range("D5").Value ' Vessel Name Sheet1.Range("B11").Value = Sheet7.Range("E5").Value ' POR Sheet1.Range("C11").Value = Sheet7.Range("F5").Value ' GRT Sheet1.Range("D11").Value = Sheet7.Range("G5").Value ' NETT Sheet1.Range("E11").Value = Sheet7.Range("H5").Value ' S.DRAFT Sheet1.Range("F11").Value = Sheet7.Range("I5").Value ' S. DISPL Sheet1.Range("G11").Value = Sheet7.Range("J5").Value ' S.DWT Sheet1.Range("H11").Value = Sheet7.Range("K5").Value ' LIGHTSHIP Sheet1.Range("I11").Value = Sheet7.Range("L5").Value ' LBP Sheet1.Range("J11").Value = Sheet7.Range("M5").Value ' BEAM Sheet1.Range("K11").Value = Sheet7.Range("N5").Value ' LOA Sheet1.Range("L11").Value = Sheet7.Range("O5").Value ' FWA Sheet1.Range("B13").Value = Sheet7.Range("P5").Value ' CH1 Sheet1.Range("C13").Value = Sheet7.Range("Q5").Value ' CH2 Sheet1.Range("D13").Value = Sheet7.Range("R5").Value ' CH3 Sheet1.Range("E13").Value = Sheet7.Range("S5").Value ' CH4 Sheet1.Range("F13").Value = Sheet7.Range("T5").Value ' CH5 Sheet1.Range("G13").Value = Sheet7.Range("U5").Value ' CH6 Sheet1.Range("H13").Value = Sheet7.Range("V5").Value ' CH7 Sheet1.Range("I13").Value = Sheet7.Range("W5").Value ' CH8 Sheet1.Range("J13").Value = Sheet7.Range("X5").Value ' CH9 Sheet1.Range("L13").Value = Sheet7.Range("Y5").Value 'FWD Sheet1.Range("M13").Value = Sheet7.Range("Z5").Value ' MID Sheet1.Range("N13").Value = Sheet7.Range("AA5").Value ' AFT Sheet1.Range("B14").Value = Sheet7.Range("AB5").Value ' CH1 Sheet1.Range("C14").Value = Sheet7.Range("AC5").Value ' CH2 Sheet1.Range("D14").Value = Sheet7.Range("AD5").Value ' CH3 Sheet1.Range("E14").Value = Sheet7.Range("AE5").Value ' CH4 Sheet1.Range("F14").Value = Sheet7.Range("AF5").Value ' CH5 Sheet1.Range("G14").Value = Sheet7.Range("AG5").Value ' CH6 Sheet1.Range("H14").Value = Sheet7.Range("AH5").Value ' CH7 Sheet1.Range("I14").Value = Sheet7.Range("AI5").Value ' CH8 Sheet1.Range("J14").Value = Sheet7.Range("AJ5").Value ' CH9 Sheet1.Range("L14").Value = Sheet7.Range("AK5").Value 'FWD Sheet1.Range("M14").Value = Sheet7.Range("AL5").Value ' MID Sheet1.Range("N14").Value = Sheet7.Range("AM5").Value ' AFT ' ' ' Loading Vessel Displacement onto the Hydrostatic Table Sheet2.Range("C2").Value = Sheet7.Range("AN5").Value ' 2.0 Sheet2.Range("D2").Value = Sheet7.Range("AO5").Value ' 2.2 Sheet2.Range("E2").Value = Sheet7.Range("AP5").Value ' 2.4 Sheet2.Range("F2").Value = Sheet7.Range("AQ5").Value ' 2.6 Sheet2.Range("G2").Value = Sheet7.Range("AR5").Value ' 2.8 Sheet2.Range("H2").Value = Sheet7.Range("AS5").Value ' 3.0 Sheet2.Range("I2").Value = Sheet7.Range("AT5").Value ' 3.2 Sheet2.Range("J2").Value = Sheet7.Range("AU5").Value ' 3.4 Sheet2.Range("K2").Value = Sheet7.Range("AV5").Value ' 3.6 Sheet2.Range("L2").Value = Sheet7.Range("AW5").Value ' 3.8 Sheet2.Range("M2").Value = Sheet7.Range("AX5").Value ' 4.0 Sheet2.Range("N2").Value = Sheet7.Range("AY5").Value ' 4.2 Sheet2.Range("O2").Value = Sheet7.Range("AZ5").Value ' 4.4 Sheet2.Range("P2").Value = Sheet7.Range("BA5").Value ' 4.6 Sheet2.Range("Q2").Value = Sheet7.Range("BB5").Value ' 4.8 Sheet2.Range("R2").Value = Sheet7.Range("BC5").Value ' 5.0 Sheet2.Range("S2").Value = Sheet7.Range("BD5").Value ' 5.2 Sheet2.Range("T2").Value = Sheet7.Range("BE5").Value ' 5.4 Sheet2.Range("U2").Value = Sheet7.Range("BF5").Value ' 5.6 Sheet2.Range("V2").Value = Sheet7.Range("BG5").Value ' 5.8 Sheet2.Range("W2").Value = Sheet7.Range("BH5").Value ' 6.0 Sheet2.Range("X2").Value = Sheet7.Range("BI5").Value ' 6.2 Sheet2.Range("Y2").Value = Sheet7.Range("BJ5").Value ' 6.4 Sheet2.Range("Z2").Value = Sheet7.Range("BK5").Value ' 6.6 Sheet2.Range("AA2").Value = Sheet7.Range("BL5").Value ' 6.8 Sheet2.Range("AB2").Value = Sheet7.Range("BM5").Value ' 7.0 Sheet2.Range("AC2").Value = Sheet7.Range("BN5").Value ' 7.2 Sheet2.Range("AD2").Value = Sheet7.Range("BO5").Value ' 7.4 Sheet2.Range("AE2").Value = Sheet7.Range("BP5").Value ' 7.6 Sheet2.Range("AF2").Value = Sheet7.Range("BQ5").Value ' 7.8 Sheet2.Range("AG2").Value = Sheet7.Range("BR5").Value ' 8.0 Sheet2.Range("AH2").Value = Sheet7.Range("BS5").Value ' 8.2 Sheet2.Range("AI2").Value = Sheet7.Range("BT5").Value ' 8.4 Sheet2.Range("AJ2").Value = Sheet7.Range("BU5").Value ' 8.6 Sheet2.Range("AK2").Value = Sheet7.Range("BV5").Value ' 8.8 Sheet2.Range("AL2").Value = Sheet7.Range("BW5").Value ' 9.0 Sheet2.Range("AM2").Value = Sheet7.Range("BX5").Value ' 9.2 Sheet2.Range("AN2").Value = Sheet7.Range("BY5").Value ' 9.4 Sheet2.Range("AO2").Value = Sheet7.Range("BZ5").Value ' 9.6 Sheet2.Range("AP2").Value = Sheet7.Range("CA5").Value ' 9.8 Sheet2.Range("AQ2").Value = Sheet7.Range("CB5").Value ' 10.0 Sheet2.Range("AR2").Value = Sheet7.Range("CC5").Value ' 10.2 Sheet2.Range("AS2").Value = Sheet7.Range("CD5").Value ' 10.4 Sheet2.Range("AT2").Value = Sheet7.Range("CE5").Value ' 10.6 Sheet2.Range("AU2").Value = Sheet7.Range("CF5").Value ' 10.8 Sheet2.Range("AV2").Value = Sheet7.Range("CG5").Value ' 11.0 Sheet2.Range("AW2").Value = Sheet7.Range("CH5").Value ' 11.2 Sheet2.Range("AX2").Value = Sheet7.Range("CI5").Value ' 11.4 Sheet2.Range("AY2").Value = Sheet7.Range("CJ5").Value ' 11.6 Sheet2.Range("AZ2").Value = Sheet7.Range("CK5").Value ' 11.8 Sheet2.Range("BA2").Value = Sheet7.Range("CL5").Value ' 12.0 Sheet2.Range("BB2").Value = Sheet7.Range("CM5").Value ' 12.2 Sheet2.Range("BC2").Value = Sheet7.Range("CN5").Value ' 12.4 Sheet2.Range("BD2").Value = Sheet7.Range("CO5").Value ' 12.6 Sheet2.Range("BE2").Value = Sheet7.Range("CP5").Value ' 12.8 Sheet2.Range("BF2").Value = Sheet7.Range("CQ5").Value ' 13.0 Sheet2.Range("BG2").Value = Sheet7.Range("CR5").Value ' 13.2 Sheet2.Range("BH2").Value = Sheet7.Range("CS5").Value ' 13.4 Sheet2.Range("BI2").Value = Sheet7.Range("CT5").Value ' 13.6 Sheet2.Range("BJ2").Value = Sheet7.Range("CU5").Value ' 13.8 Sheet2.Range("BK2").Value = Sheet7.Range("CV5").Value ' 14.0 Sheet2.Range("BL2").Value = Sheet7.Range("CW5").Value ' 14.2 Sheet2.Range("BM2").Value = Sheet7.Range("CX5").Value ' 14.4 Sheet2.Range("BN2").Value = Sheet7.Range("CY5").Value ' 14.6 Sheet2.Range("BO2").Value = Sheet7.Range("CZ5").Value ' 14.8 Sheet2.Range("BP2").Value = Sheet7.Range("DA5").Value ' 15.0 Sheet2.Range("BQ2").Value = Sheet7.Range("DB5").Value ' 15.2 Sheet2.Range("BR2").Value = Sheet7.Range("DC5").Value ' 15.4 Sheet2.Range("BS2").Value = Sheet7.Range("DD5").Value ' 15.6 Sheet2.Range("BT2").Value = Sheet7.Range("DE5").Value ' 15.8 Sheet2.Range("BU2").Value = Sheet7.Range("DF5").Value ' 16.0 Sheet2.Range("BV2").Value = Sheet7.Range("DG5").Value ' 16.2 Sheet2.Range("BW2").Value = Sheet7.Range("DH5").Value ' 16.4 Sheet2.Range("BX2").Value = Sheet7.Range("DI5").Value ' 16.6 Sheet2.Range("BY2").Value = Sheet7.Range("DJ5").Value ' 16.8 Sheet2.Range("BZ2").Value = Sheet7.Range("DK5").Value ' 17.0 Sheet2.Range("CA2").Value = Sheet7.Range("DL5").Value ' 17.2 Sheet2.Range("CB2").Value = Sheet7.Range("DM5").Value ' 17.4 Sheet2.Range("CC2").Value = Sheet7.Range("DN5").Value ' 17.6 Sheet2.Range("CD2").Value = Sheet7.Range("DO5").Value ' 17.8 Sheet2.Range("CE2").Value = Sheet7.Range("DP5").Value ' 18.0 Sheet2.Range("CF2").Value = Sheet7.Range("DQ5").Value ' 18.2 Sheet2.Range("CG2").Value = Sheet7.Range("DR5").Value ' 18.4 Sheet2.Range("CH2").Value = Sheet7.Range("DS5").Value ' 18.6 Sheet2.Range("CI2").Value = Sheet7.Range("DT5").Value ' 18.8 Sheet2.Range("CJ2").Value = Sheet7.Range("DU5").Value ' 19.0 Sheet2.Range("CK2").Value = Sheet7.Range("DV5").Value ' 19.2 Sheet2.Range("CL2").Value = Sheet7.Range("DW5").Value ' 19.4 Sheet2.Range("CM2").Value = Sheet7.Range("DX5").Value ' 19.6 Sheet2.Range("CN2").Value = Sheet7.Range("DY5").Value ' 19.8 Sheet2.Range("CO2").Value = Sheet7.Range("DZ5").Value ' 20.0 ' ' ' Loading Vessel TPC onto the Hydrostatic Table Sheet2.Range("C3").Value = Sheet7.Range("EA5").Value ' 2.0 Sheet2.Range("D3").Value = Sheet7.Range("EB5").Value ' 2.2 Sheet2.Range("E3").Value = Sheet7.Range("EC5").Value ' 2.4 Sheet2.Range("F3").Value = Sheet7.Range("ED5").Value ' 2.6 Sheet2.Range("G3").Value = Sheet7.Range("EE5").Value ' 2.8 Sheet2.Range("H3").Value = Sheet7.Range("AF5").Value ' 3.0 Sheet2.Range("I3").Value = Sheet7.Range("EG5").Value ' 3.2 Sheet2.Range("J3").Value = Sheet7.Range("EH5").Value ' 3.4 Sheet2.Range("K3").Value = Sheet7.Range("EI5").Value ' 3.6 Sheet2.Range("L3").Value = Sheet7.Range("EJ5").Value ' 3.8 Sheet2.Range("M3").Value = Sheet7.Range("EK5").Value ' 4.0 Sheet2.Range("N3").Value = Sheet7.Range("EL5").Value ' 4.2 Sheet2.Range("O3").Value = Sheet7.Range("EM5").Value ' 4.4 Sheet2.Range("P3").Value = Sheet7.Range("EN5").Value ' 4.6 Sheet2.Range("Q3").Value = Sheet7.Range("EO5").Value ' 4.8 Sheet2.Range("R3").Value = Sheet7.Range("EP5").Value ' 5.0 Sheet2.Range("S3").Value = Sheet7.Range("EQ5").Value ' 5.2 Sheet2.Range("T3").Value = Sheet7.Range("ER5").Value ' 5.4 Sheet2.Range("U3").Value = Sheet7.Range("ES5").Value ' 5.6 Sheet2.Range("V3").Value = Sheet7.Range("ET5").Value ' 5.8 Sheet2.Range("W3").Value = Sheet7.Range("EU5").Value ' 6.0 Sheet2.Range("X3").Value = Sheet7.Range("EV5").Value ' 6.2 Sheet2.Range("Y3").Value = Sheet7.Range("EW5").Value ' 6.4 Sheet2.Range("Z3").Value = Sheet7.Range("EX5").Value ' 6.6 Sheet2.Range("AA3").Value = Sheet7.Range("EY5").Value ' 6.8 Sheet2.Range("AB3").Value = Sheet7.Range("EZ5").Value ' 7.0 Sheet2.Range("AC3").Value = Sheet7.Range("FA5").Value ' 7.2 Sheet2.Range("AD3").Value = Sheet7.Range("FB5").Value ' 7.4 Sheet2.Range("AE3").Value = Sheet7.Range("FC5").Value ' 7.6 Sheet2.Range("AF3").Value = Sheet7.Range("FD5").Value ' 7.8 Sheet2.Range("AG3").Value = Sheet7.Range("FE5").Value ' 8.0 Sheet2.Range("AH3").Value = Sheet7.Range("FF5").Value ' 8.2 Sheet2.Range("AI3").Value = Sheet7.Range("FG5").Value ' 8.4 Sheet2.Range("AJ3").Value = Sheet7.Range("FH5").Value ' 8.6 Sheet2.Range("AK3").Value = Sheet7.Range("FI5").Value ' 8.8 Sheet2.Range("AL3").Value = Sheet7.Range("FJ5").Value ' 9.0 Sheet2.Range("AM3").Value = Sheet7.Range("FK5").Value ' 9.2 Sheet2.Range("AN3").Value = Sheet7.Range("FL5").Value ' 9.4 Sheet2.Range("AO3").Value = Sheet7.Range("FM5").Value ' 9.6 Sheet2.Range("AP3").Value = Sheet7.Range("FN5").Value ' 9.8 Sheet2.Range("AQ3").Value = Sheet7.Range("FO5").Value ' 10.0 Sheet2.Range("AR3").Value = Sheet7.Range("FP5").Value ' 10.2 Sheet2.Range("AS3").Value = Sheet7.Range("FQ5").Value ' 10.4 Sheet2.Range("AT3").Value = Sheet7.Range("FR5").Value ' 10.6 Sheet2.Range("AU3").Value = Sheet7.Range("FS5").Value ' 10.8 Sheet2.Range("AV3").Value = Sheet7.Range("FT5").Value ' 11.0 Sheet2.Range("AW3").Value = Sheet7.Range("FU5").Value ' 11.2 Sheet2.Range("AX3").Value = Sheet7.Range("FV5").Value ' 11.4 Sheet2.Range("AY3").Value = Sheet7.Range("FW5").Value ' 11.6 Sheet2.Range("AZ3").Value = Sheet7.Range("FX5").Value ' 11.8 Sheet2.Range("BA3").Value = Sheet7.Range("FY5").Value ' 12.0 Sheet2.Range("BB3").Value = Sheet7.Range("FZ5").Value ' 12.2 Sheet2.Range("BC3").Value = Sheet7.Range("GA5").Value ' 12.4 Sheet2.Range("BD3").Value = Sheet7.Range("GB5").Value ' 12.6 Sheet2.Range("BE2").Value = Sheet7.Range("GC5").Value ' 12.8 Sheet2.Range("BF2").Value = Sheet7.Range("GD5").Value ' 13.0 Sheet2.Range("BG2").Value = Sheet7.Range("GE5").Value ' 13.2 Sheet2.Range("BH3").Value = Sheet7.Range("GF5").Value ' 13.4 Sheet2.Range("BI3").Value = Sheet7.Range("GG5").Value ' 13.6 Sheet2.Range("BJ3").Value = Sheet7.Range("GH5").Value ' 13.8 Sheet2.Range("BK3").Value = Sheet7.Range("GI5").Value ' 14.0 Sheet2.Range("BL3").Value = Sheet7.Range("GJ5").Value ' 14.2 Sheet2.Range("BM3").Value = Sheet7.Range("GK5").Value ' 14.4 Sheet2.Range("BN3").Value = Sheet7.Range("GL5").Value ' 14.6 Sheet2.Range("BO3").Value = Sheet7.Range("GM5").Value ' 14.8 Sheet2.Range("BP3").Value = Sheet7.Range("GN5").Value ' 15.0 Sheet2.Range("BQ3").Value = Sheet7.Range("GO5").Value ' 15.2 Sheet2.Range("BR3").Value = Sheet7.Range("GP5").Value ' 15.4 Sheet2.Range("BS3").Value = Sheet7.Range("GQ5").Value ' 15.6 Sheet2.Range("BT3").Value = Sheet7.Range("GR5").Value ' 15.8 Sheet2.Range("BU3").Value = Sheet7.Range("GS5").Value ' 16.0 Sheet2.Range("BV3").Value = Sheet7.Range("GT5").Value ' 16.2 Sheet2.Range("BW3").Value = Sheet7.Range("GU5").Value ' 16.4 Sheet2.Range("BX3").Value = Sheet7.Range("GV5").Value ' 16.6 Sheet2.Range("BY3").Value = Sheet7.Range("GW5").Value ' 16.8 Sheet2.Range("BZ3").Value = Sheet7.Range("GX5").Value ' 17.0 Sheet2.Range("CA3").Value = Sheet7.Range("GY5").Value ' 17.2 Sheet2.Range("CB3").Value = Sheet7.Range("GZ5").Value ' 17.4 Sheet2.Range("CC3").Value = Sheet7.Range("HA5").Value ' 17.6 Sheet2.Range("CD3").Value = Sheet7.Range("HB5").Value ' 17.8 Sheet2.Range("CE3").Value = Sheet7.Range("HC5").Value ' 18.0 Sheet2.Range("CF3").Value = Sheet7.Range("HD5").Value ' 18.2 Sheet2.Range("CG3").Value = Sheet7.Range("HE5").Value ' 18.4 Sheet2.Range("CH3").Value = Sheet7.Range("HF5").Value ' 18.6 Sheet2.Range("CI3").Value = Sheet7.Range("HG5").Value ' 18.8 Sheet2.Range("CJ3").Value = Sheet7.Range("HH5").Value ' 19.0 Sheet2.Range("CK3").Value = Sheet7.Range("HI5").Value ' 19.2 Sheet2.Range("CL3").Value = Sheet7.Range("HJ5").Value ' 19.4 Sheet2.Range("CM3").Value = Sheet7.Range("HK5").Value ' 19.6 Sheet2.Range("CN3").Value = Sheet7.Range("HL5").Value ' 19.8 Sheet2.Range("CO3").Value = Sheet7.Range("HM5").Value ' 20.0 ' ' ' Loading Vessel LCF onto the Hydrostatic Table Sheet2.Range("C4").Value = Sheet7.Range("HN5").Value ' 2.0 Sheet2.Range("D4").Value = Sheet7.Range("HO5").Value ' 2.2 Sheet2.Range("E4").Value = Sheet7.Range("HP5").Value ' 2.4 Sheet2.Range("F4").Value = Sheet7.Range("HQ5").Value ' 2.6 Sheet2.Range("G4").Value = Sheet7.Range("HR5").Value ' 2.8 Sheet2.Range("H4").Value = Sheet7.Range("HS5").Value ' 3.0 Sheet2.Range("I4").Value = Sheet7.Range("HT5").Value ' 3.2 Sheet2.Range("J4").Value = Sheet7.Range("HU5").Value ' 3.4 Sheet2.Range("K4").Value = Sheet7.Range("HV5").Value ' 3.6 Sheet2.Range("L4").Value = Sheet7.Range("HW5").Value ' 3.8 Sheet2.Range("M4").Value = Sheet7.Range("HX5").Value ' 4.0 Sheet2.Range("N4").Value = Sheet7.Range("HY5").Value ' 4.2 Sheet2.Range("O4").Value = Sheet7.Range("HZ5").Value ' 4.4 Sheet2.Range("P4").Value = Sheet7.Range("IA5").Value ' 4.6 Sheet2.Range("Q4").Value = Sheet7.Range("IB5").Value ' 4.8 Sheet2.Range("R4").Value = Sheet7.Range("IC5").Value ' 5.0 Sheet2.Range("S4").Value = Sheet7.Range("ID5").Value ' 5.2 Sheet2.Range("T4").Value = Sheet7.Range("IE5").Value ' 5.4 Sheet2.Range("U4").Value = Sheet7.Range("IF5").Value ' 5.6 Sheet2.Range("V4").Value = Sheet7.Range("IG5").Value ' 5.8 Sheet2.Range("W4").Value = Sheet7.Range("IH5").Value ' 6.0 Sheet2.Range("X4").Value = Sheet7.Range("II5").Value ' 6.2 Sheet2.Range("Y4").Value = Sheet7.Range("IJ5").Value ' 6.4 Sheet2.Range("Z4").Value = Sheet7.Range("IK5").Value ' 6.6 Sheet2.Range("AA4").Value = Sheet7.Range("IL5").Value ' 6.8 Sheet2.Range("AB4").Value = Sheet7.Range("IM5").Value ' 7.0 Sheet2.Range("AC4").Value = Sheet7.Range("IN5").Value ' 7.2 Sheet2.Range("AD4").Value = Sheet7.Range("IO5").Value ' 7.4 Sheet2.Range("AE4").Value = Sheet7.Range("IP5").Value ' 7.6 Sheet2.Range("AF4").Value = Sheet7.Range("IQ5").Value ' 7.8 Sheet2.Range("AG4").Value = Sheet7.Range("IR5").Value ' 8.0 Sheet2.Range("AH4").Value = Sheet7.Range("IS5").Value ' 8.2 Sheet2.Range("AI4").Value = Sheet7.Range("IT5").Value ' 8.4 Sheet2.Range("AJ4").Value = Sheet7.Range("IU5").Value ' 8.6 Sheet2.Range("AK4").Value = Sheet7.Range("IV5").Value ' 8.8 Sheet2.Range("AL4").Value = Sheet7.Range("IW5").Value ' 9.0 Sheet2.Range("AM4").Value = Sheet7.Range("IX5").Value ' 9.2 Sheet2.Range("AN4").Value = Sheet7.Range("IY5").Value ' 9.4 Sheet2.Range("AO4").Value = Sheet7.Range("IZ5").Value ' 9.6 Sheet2.Range("AP4").Value = Sheet7.Range("JA5").Value ' 9.8 Sheet2.Range("AQ4").Value = Sheet7.Range("JB5").Value ' 10.0 Sheet2.Range("AR4").Value = Sheet7.Range("JC5").Value ' 10.2 Sheet2.Range("AS4").Value = Sheet7.Range("JD5").Value ' 10.4 Sheet2.Range("AT4").Value = Sheet7.Range("JE5").Value ' 10.6 Sheet2.Range("AU4").Value = Sheet7.Range("JF5").Value ' 10.8 Sheet2.Range("AV4").Value = Sheet7.Range("JG5").Value ' 11.0 Sheet2.Range("AW4").Value = Sheet7.Range("JH5").Value ' 11.2 Sheet2.Range("AX4").Value = Sheet7.Range("JI5").Value ' 11.4 Sheet2.Range("AY4").Value = Sheet7.Range("JJ5").Value ' 11.6 Sheet2.Range("AZ4").Value = Sheet7.Range("JK5").Value ' 11.8 Sheet2.Range("BA4").Value = Sheet7.Range("JL5").Value ' 12.0 Sheet2.Range("BB4").Value = Sheet7.Range("JM5").Value ' 12.2 Sheet2.Range("BC4").Value = Sheet7.Range("JN5").Value ' 12.4 Sheet2.Range("BD4").Value = Sheet7.Range("JO5").Value ' 12.6 Sheet2.Range("BE4").Value = Sheet7.Range("JP5").Value ' 12.8 Sheet2.Range("BF2").Value = Sheet7.Range("JQ5").Value ' 13.0 Sheet2.Range("BG4").Value = Sheet7.Range("JR5").Value ' 13.2 Sheet2.Range("BH4").Value = Sheet7.Range("JS5").Value ' 13.4 Sheet2.Range("BI4").Value = Sheet7.Range("JT5").Value ' 13.6 Sheet2.Range("BJ4").Value = Sheet7.Range("JU5").Value ' 13.8 Sheet2.Range("BK4").Value = Sheet7.Range("JV5").Value ' 14.0 Sheet2.Range("BL4").Value = Sheet7.Range("JW5").Value ' 14.2 Sheet2.Range("BM4").Value = Sheet7.Range("JX5").Value ' 14.4 Sheet2.Range("BN4").Value = Sheet7.Range("JY5").Value ' 14.6 Sheet2.Range("BO4").Value = Sheet7.Range("JZ5").Value ' 14.8 Sheet2.Range("BP4").Value = Sheet7.Range("KA5").Value ' 15.0 Sheet2.Range("BQ4").Value = Sheet7.Range("KB5").Value ' 15.2 Sheet2.Range("BR4").Value = Sheet7.Range("KC5").Value ' 15.4 Sheet2.Range("BS4").Value = Sheet7.Range("KD5").Value ' 15.6 Sheet2.Range("BT4").Value = Sheet7.Range("KE5").Value ' 15.8 Sheet2.Range("BU4").Value = Sheet7.Range("KF5").Value ' 16.0 Sheet2.Range("BV4").Value = Sheet7.Range("KG5").Value ' 16.2 Sheet2.Range("BW4").Value = Sheet7.Range("KH5").Value ' 16.4 Sheet2.Range("BX4").Value = Sheet7.Range("KI5").Value ' 16.6 Sheet2.Range("BY4").Value = Sheet7.Range("KJ5").Value ' 16.8 Sheet2.Range("BZ4").Value = Sheet7.Range("KK5").Value ' 17.0 Sheet2.Range("CA4").Value = Sheet7.Range("KL5").Value ' 17.2 Sheet2.Range("CB4").Value = Sheet7.Range("KM5").Value ' 17.4 Sheet2.Range("CC4").Value = Sheet7.Range("KN5").Value ' 17.6 Sheet2.Range("CD4").Value = Sheet7.Range("KO5").Value ' 17.8 Sheet2.Range("CE4").Value = Sheet7.Range("KP5").Value ' 18.0 Sheet2.Range("CF4").Value = Sheet7.Range("KQ5").Value ' 18.2 Sheet2.Range("CG4").Value = Sheet7.Range("KR5").Value ' 18.4 Sheet2.Range("CH4").Value = Sheet7.Range("KS5").Value ' 18.6 Sheet2.Range("CI4").Value = Sheet7.Range("KT5").Value ' 18.8 Sheet2.Range("CJ4").Value = Sheet7.Range("KU5").Value ' 19.0 Sheet2.Range("CK4").Value = Sheet7.Range("KV5").Value ' 19.2 Sheet2.Range("CL4").Value = Sheet7.Range("KW5").Value ' 19.4 Sheet2.Range("CM4").Value = Sheet7.Range("KX5").Value ' 19.6 Sheet2.Range("CN4").Value = Sheet7.Range("KY5").Value ' 19.8 Sheet2.Range("CO4").Value = Sheet7.Range("KZ5").Value ' 20.0 ' ' ' Loading Vessel MTC onto the Hydrostatic Table Sheet2.Range("C5").Value = Sheet7.Range("LA5").Value ' 2.0 Sheet2.Range("D5").Value = Sheet7.Range("LB5").Value ' 2.2 Sheet2.Range("E5").Value = Sheet7.Range("LC5").Value ' 2.4 Sheet2.Range("F5").Value = Sheet7.Range("LD5").Value ' 2.6 Sheet2.Range("G5").Value = Sheet7.Range("LE5").Value ' 2.8 Sheet2.Range("H5").Value = Sheet7.Range("LF5").Value ' 3.0 Sheet2.Range("I5").Value = Sheet7.Range("LG5").Value ' 3.2 Sheet2.Range("J5").Value = Sheet7.Range("LH5").Value ' 3.4 Sheet2.Range("K5").Value = Sheet7.Range("LI5").Value ' 3.6 Sheet2.Range("L5").Value = Sheet7.Range("LJ5").Value ' 3.8 Sheet2.Range("M5").Value = Sheet7.Range("LK5").Value ' 4.0 Sheet2.Range("N5").Value = Sheet7.Range("LL5").Value ' 4.2 Sheet2.Range("O5").Value = Sheet7.Range("LM5").Value ' 4.4 Sheet2.Range("P5").Value = Sheet7.Range("LN5").Value ' 4.6 Sheet2.Range("Q5").Value = Sheet7.Range("LO5").Value ' 4.8 Sheet2.Range("R5").Value = Sheet7.Range("LP5").Value ' 5.0 Sheet2.Range("S5").Value = Sheet7.Range("LQ5").Value ' 5.2 Sheet2.Range("T5").Value = Sheet7.Range("LR5").Value ' 5.4 Sheet2.Range("U5").Value = Sheet7.Range("LS5").Value ' 5.6 Sheet2.Range("V5").Value = Sheet7.Range("LT5").Value ' 5.8 Sheet2.Range("W5").Value = Sheet7.Range("LU5").Value ' 6.0 Sheet2.Range("X5").Value = Sheet7.Range("LV5").Value ' 6.2 Sheet2.Range("Y5").Value = Sheet7.Range("LW5").Value ' 6.4 Sheet2.Range("Z5").Value = Sheet7.Range("LX5").Value ' 6.6 Sheet2.Range("AA5").Value = Sheet7.Range("LY5").Value ' 6.8 Sheet2.Range("AB5").Value = Sheet7.Range("LZ5").Value ' 7.0 Sheet2.Range("AC5").Value = Sheet7.Range("MA5").Value ' 7.2 Sheet2.Range("AD5").Value = Sheet7.Range("MB5").Value ' 7.4 Sheet2.Range("AE5").Value = Sheet7.Range("MC5").Value ' 7.6 Sheet2.Range("AF5").Value = Sheet7.Range("MD5").Value ' 7.8 Sheet2.Range("AG2").Value = Sheet7.Range("ME5").Value ' 8.0 Sheet2.Range("AH5").Value = Sheet7.Range("MF5").Value ' 8.2 Sheet2.Range("AI5").Value = Sheet7.Range("MG5").Value ' 8.4 Sheet2.Range("AJ5").Value = Sheet7.Range("MH5").Value ' 8.6 Sheet2.Range("AK5").Value = Sheet7.Range("MI5").Value ' 8.8 Sheet2.Range("AL5").Value = Sheet7.Range("MJ5").Value ' 9.0 Sheet2.Range("AM5").Value = Sheet7.Range("MK5").Value ' 9.2 Sheet2.Range("AN5").Value = Sheet7.Range("ML5").Value ' 9.4 Sheet2.Range("AO5").Value = Sheet7.Range("MM5").Value ' 9.6 Sheet2.Range("AP5").Value = Sheet7.Range("MN5").Value ' 9.8 Sheet2.Range("AQ5").Value = Sheet7.Range("MO5").Value ' 10.0 Sheet2.Range("AR5").Value = Sheet7.Range("MP5").Value ' 10.2 Sheet2.Range("AS5").Value = Sheet7.Range("MQ5").Value ' 10.4 Sheet2.Range("AT5").Value = Sheet7.Range("MR5").Value ' 10.6 Sheet2.Range("AU5").Value = Sheet7.Range("MS5").Value ' 10.8 Sheet2.Range("AV5").Value = Sheet7.Range("MT5").Value ' 11.0 Sheet2.Range("AW5").Value = Sheet7.Range("MU5").Value ' 11.2 Sheet2.Range("AX2").Value = Sheet7.Range("MV5").Value ' 11.4 Sheet2.Range("AY5").Value = Sheet7.Range("MW5").Value ' 11.6 Sheet2.Range("AZ2").Value = Sheet7.Range("MX5").Value ' 11.8 Sheet2.Range("BA5").Value = Sheet7.Range("MY5").Value ' 12.0 Sheet2.Range("BB5").Value = Sheet7.Range("MZ5").Value ' 12.2 Sheet2.Range("BC5").Value = Sheet7.Range("NA5").Value ' 12.4 Sheet2.Range("BD5").Value = Sheet7.Range("NB5").Value ' 12.6 Sheet2.Range("BE5").Value = Sheet7.Range("NC5").Value ' 12.8 Sheet2.Range("BF5").Value = Sheet7.Range("ND5").Value ' 13.0 Sheet2.Range("BG5").Value = Sheet7.Range("NE5").Value ' 13.2 Sheet2.Range("BH5").Value = Sheet7.Range("NF5").Value ' 13.4 Sheet2.Range("BI5").Value = Sheet7.Range("NG5").Value ' 13.6 Sheet2.Range("BJ5").Value = Sheet7.Range("NH5").Value ' 13.8 Sheet2.Range("BK5").Value = Sheet7.Range("NI5").Value ' 14.0 Sheet2.Range("BL5").Value = Sheet7.Range("NJ5").Value ' 14.2 Sheet2.Range("BM5").Value = Sheet7.Range("NK5").Value ' 14.4 Sheet2.Range("BN5").Value = Sheet7.Range("NL5").Value ' 14.6 Sheet2.Range("BO5").Value = Sheet7.Range("NM5").Value ' 14.8 Sheet2.Range("BP5").Value = Sheet7.Range("NN5").Value ' 15.0 Sheet2.Range("BQ5").Value = Sheet7.Range("NO5").Value ' 15.2 Sheet2.Range("BR5").Value = Sheet7.Range("NP5").Value ' 15.4 Sheet2.Range("BS5").Value = Sheet7.Range("NQ5").Value ' 15.6 Sheet2.Range("BT5").Value = Sheet7.Range("NR5").Value ' 15.8 Sheet2.Range("BU5").Value = Sheet7.Range("NS5").Value ' 16.0 Sheet2.Range("BV5").Value = Sheet7.Range("NT5").Value ' 16.2 Sheet2.Range("BW5").Value = Sheet7.Range("NU5").Value ' 16.4 Sheet2.Range("BX5").Value = Sheet7.Range("NV5").Value ' 16.6 Sheet2.Range("BY5").Value = Sheet7.Range("NW5").Value ' 16.8 Sheet2.Range("BZ5").Value = Sheet7.Range("NX5").Value ' 17.0 Sheet2.Range("CA5").Value = Sheet7.Range("NY5").Value ' 17.2 Sheet2.Range("CB5").Value = Sheet7.Range("NZ5").Value ' 17.4 Sheet2.Range("CC5").Value = Sheet7.Range("OA5").Value ' 17.6 Sheet2.Range("CD5").Value = Sheet7.Range("OB5").Value ' 17.8 Sheet2.Range("CE5").Value = Sheet7.Range("OC5").Value ' 18.0 Sheet2.Range("CF5").Value = Sheet7.Range("OD5").Value ' 18.2 Sheet2.Range("CG5").Value = Sheet7.Range("OE5").Value ' 18.4 Sheet2.Range("CH5").Value = Sheet7.Range("OF5").Value ' 18.6 Sheet2.Range("CI5").Value = Sheet7.Range("OG5").Value ' 18.8 Sheet2.Range("CJ5").Value = Sheet7.Range("OH5").Value ' 19.0 Sheet2.Range("CK5").Value = Sheet7.Range("OI5").Value ' 19.2 Sheet2.Range("CL5").Value = Sheet7.Range("OJ5").Value ' 19.4 Sheet2.Range("CM5").Value = Sheet7.Range("OK5").Value ' 19.6 Sheet2.Range("CN5").Value = Sheet7.Range("OL5").Value ' 19.8 Sheet2.Range("CO5").Value = Sheet7.Range("OM5").Value ' 20.0 ' End Sub
Solved by T. H. in 30 mins
Trying to match a value from one spreadsheet to a value in a range of another and return corresponding value
Solved by D. L. in 22 mins
I'm trying to countif a range of cells contains the value of cell a1
Solved by K. B. in 20 mins

Leave a Comment

avatar