Go Back

How to Check If Range Contains a Value Not in Another Range in Excel

We often compare one range values with another range to check for duplicates. But how to check if one range values exist in another range in Excel? We will learn in this article how to check if a range contains a value not in another range in Excel.

Figure 1. Check If a Range Contains a Value Not in Another Range

Formula Syntax

The generic formula syntax is;

=SUMPRODUCT(--(ISNA(MATCH(range1,range2,0))))>0

In this formula, we use the SUMPRODUCT function along with MATCH and ISNA function. This formula checks if range one contains at least one or more values that are not part of another range and returns TRUE, else it returns FALSE.

Figure 2. Formula Syntax

Suppose we have two lists of students’ roll numbers and we want to check if List A contains any values (one or more values) that are not in List B. Hence, the formula compares List A (range B2: B11) with List B (range C2: C11) and returns TRUE if List A contains at least one or more values not in List B, such as;

=SUMPRODUCT(--(ISNA(MATCH(B2:B11,C2:C11,0))))>0

Figure 3. The Formula Result

How Formula Works

The MATCH function returns the relative positions of range B2: B11 values that exactly match in range C2: C11, and returns the #NA error value(s) where range B2: B11 values are not found in range C2: C11 as an array. Select the MATCH part of the formula in the formula bar and press F9 to view the resulting array.

Figure 4. Resulting Array of the MATCH Function

The ISNA function checks for the #NA error values and returns TRUE, else returns FALSE. Hence, it converts the array returned by the MATCH function into an array of TRUE and FALSE logical values, such as;

Figure 5. The array of Logical Values Return by the ISNA Function

The double dash (–) symbol converts the TRUE and FALSE logical values into an array of 1s and 0s. The SUMPRODUCT function sums this final array of 1s and 0s and compares the result with 0 and returns TRUE if the sum of values is greater than 0.

Figure 6. Final Array of 1s and 0s

Figure 7. Final Result of the Formula

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

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