Help to find higher and lower value theen copy to another sheet

sbv1986

Board Regular
Joined
Nov 2, 2017
Messages
75
Hi all
I have workbook with 4 sheets("datanew", "dataold","higher", "lower"), now I want to find higher/lower value then copy to sheets("higher/lower") with conditions that:


Code:
With sheets("datanew")
lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
     for i =3 to lastrow
          for j =21 to lastrow
             if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             else
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if
     next j
     next i
End with

Please help me to do this with VBa code or Pivot table, many thanks./.
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,881
I would use conditional formatting on the datanew worksheet comparing matching cells to the dataold worksheet and producing green if data new was higher and red if it was lower.

Code:
Option Explicit

Sub CopyDatanewToHiOrLow()

    Dim rngCell As Range
    Dim rngData As Range
    
    'Create a conditional format for the rngData cells on newdata that fills the cells
    '  with rgbLime (0,255,0) if the new data is greater than the old data and
    '  rgbRed (0,0,255) if the old data is greater than the new data.
    
    With Worksheets("datanew")
        lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
        lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        'You referenced lastrow twice...I made a guess which one should be lastcolumn
        Set rngData = Worksheets("datanew").Range(.Cells(3, 21), .Cells(lastrow, lastcolumn))
        
        For Each rngCell In rngData.Cells
            If rngCell.DisplayFormat.Interior.Color = rgbRed Then
                Worksheets("lower").Range(rngCell.Address).Value = _
                    .Range(rngCell.Address).Value
            ElseIf rngCell.DisplayFormat.Interior.Color = rgbLime Then
                Worksheets("higher").Range(rngCell.Address).Value = _
                    .Range(rngCell.Address).Value
            End If
        Next
    End With
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Hi all
I have workbook with 4 sheets("datanew", "dataold","higher", "lower"), now I want to find higher/lower value then copy to sheets("higher/lower") with conditions that:


Code:
With sheets("datanew")
lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
     for i =3 to lastrow
          for j =21 to [COLOR=#ff0000]lastrow[/COLOR]
             if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             else
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if
     next j
     next i
End with

Please help me to do this with VBa code or Pivot table, many thanks./.

In this line "for j =21 to lastrow" must be "lastcolumn"?


You could put an example with data of what you have and what you expect of result.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Code:
if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             [B][COLOR=#ff0000]else[/COLOR][/B]
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if
Also the Else must be End If
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,777
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top