Posts by rehan kazi

Thanks Carim for you Suggestion and thanks roy Uk for the reply

That's Great!

But how can perform this following activity in your suggested code?

'If .Offset(0, 7) ="" Then No action required
 'Otherwise Sheets("Sheet10").Range("AJ4").Value = .Offset(0, 7).Value & also Sheets("Sheet10").Range("AJ5").Value = "South"
 
 
 'If .Offset(0, 9) ="" Then No action required
 'Otherwise Sheets("Sheet10").Range("AM4").Value = .Offset(0, 9).Value & also Sheets("Sheet10").Range("AM5").Value = "East"
 
 
 'If .Offset(0, 12) ="" Then No action required
 'Otherwise Sheets("Sheet10").Range("AP4").Value = .Offset(0, 12).Value & also Sheets("Sheet10").Range("AP5").Value = "West"

Hi,

I'm trying to write code ,If the offset cell is empty then no action is required otherwise copy offset cell content and paste it into another sheet and at the same time I want one more specific word to be pasted in another cell.

So far I found the code that would only copies the offset cell and pastes it into another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D7:D130")) Is Nothing Then
  
            If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
             
             Sheets("Sheet10").Range("I3").Value = ActiveCell.Offset(0, -1).Value
             Sheets("Sheet10").Range("K3").Value = Target.Value
             Sheets("Sheet10").Range("Q3").Value = ActiveCell.Offset(0, 1).Value
             
             
            
             'If ActiveCell.Offset(0, 5) ="" Then No action required
             'Otherwise Sheets("Sheet10").Range("AG4").Value = ActiveCell.Offset(0, 5).Value & also Sheets("Sheet10").Range("AG5").Value = "North"
                         
            
             'If ActiveCell.Offset(0, 7) ="" Then Then No action required
             'Otherwise Sheets("Sheet10").Range("AJ4").Value = ActiveCell.Offset(0, 7).Value & also Sheets("Sheet10").Range("AJ5").Value = "South"
                          
             
             'If ActiveCell.Offset(0, 9) ="" Then Then No action required
             'Otherwise Sheets("Sheet10").Range("AM4").Value = ActiveCell.Offset(0, 9).Value & also Sheets("Sheet10").Range("AM5").Value = "East"
                         
            
             'If ActiveCell.Offset(0, 12) ="" Then No action required
             'Otherwise Sheets("Sheet10").Range("AP4").Value = ActiveCell.Offset(0, 12).Value & also Sheets("Sheet10").Range("AP5").Value = "West"


    End If
  End If
End Sub

Yes

That's exactly ....you did it..... Thank you very much

Max only will applicable to the Retrieval sheet column heading (column68).............. regret to confused you......So my concept is still the same, don't want to add anything more.

Sorry... again....and again.....

Also, if you make some changes to the summary sheet code to extract the same data as is currently being extracted from the Lab Result sheet and pasted into the display sheet, it will be very helpful to me.

Hi...

In Summary Sheet Row No. 11, Column Headings C, D, E and Column9 containing the cell values are COD, CCP 13, NOC 17 and 31 May 20. If we click on the cell value CCP-13 or NOC-17, then the macro starts looking for all these 4 criteria's in the Retrieval sheet Column54,55,56 & column65...... finds two matches in the Retrieval sheet row 692 and row 693.In these two rows, the cell values on column 68 are 0.264 and 0.47. I want to extract the largest value from column68 Retrieval Sheet which is 0.47. But if you look at the Display sheet, the macro is extracting the smallest value 0.264 in column68 instead of the 0.47.

Thank you

Hi Carim,

Thank you for your reply.... as you suggested, I am sharing actual complete structure with all the sheets with you. I have redesigned the macro code you provided as per my requirement, it is working fine now....but still I have two issues.

By clicking on one of the C7 to C729 summary sheet cells, the macro will run and copy data from multiple sheets and paste it into the display sheet but my VBA code performance is very slow. Is there any way to improve VBA performance and prevent slow code execution? And my second question is, In retrieval sheet there are repeated matching criteria's , so I want evaluate formula should pull the max of it.

I would be very grateful if you could solve my issues.

Thank Carim,

Is it possible that I can share my original file with you by mail?

Hello Carim,

First of all, thank you so much for spending your precious time with me. Secondly, sorry for bothering you again and again.

I need another help from your side, you already understand my concept, so it would be better if you could suggest a better structure that is compatible for coding so I will start working on it.

Hope you will find good solution

Hi Carim,

It would be great if you could solve two more problems for me. In the same sheet you worked before.

First, by pressing the button, I want to extract the data from two more sheets. Secondly, If the search criteria finds a repeated matching criteria's , then the evaluate formula should pull the max of it.

Attached sheet, I marked the data I want to extract and also marked the criteria.

Hi Carim,

Applied your version3 in my original sheet.....Work very nicely..... really appreciate your effort

I will defiantly apply this to last version.....Hope it will work too

Thank You Very Much .......Working ..Amazing

Everything working charm.... Only just Double click D4 cell not giving any result

By double click on A4 cell, .....Receiving run time error 1004

Set rng = Sheets("Display").Range(Cells(2, col), Cells(last2, col))

Display Sheet B1, C1 and D1 are three criteria's which I want to match in Retrieval Sheets. One more fourth criteria I want to match is the cell from Display sheet D6 to D25. If all Four criteria matches in Retrieval Report sheet than macro will extract offset data from specific column of Retrieval report and will paste in to the display sheet AE to AG column.

But the Paste cell in Display Sheet AE to AG column will be the offset to the fourth matched data from D6 to D25.

I am now struggling to run this macro by clicking on the RetrievalReport D4 cell instead of the button .

Sorry to say,my problem is still unresolved because of my misinterpretation. To solve my problem I created a new sheet that works fine with the push of a button, using your old macro code. Now I need a macro to run by clicking on the cell instead of the button. I have tried to run code by clicking on the cell but it is showing errors. Once this macro runs by clicking on cell then I will delete the button and run it by just clicking on the cell.This code will help me run my original sheet.

I need your help to run the macro using code below instead of button.

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Sheets(RetrievalReport).Range("D4")) Is Nothing Then
            Call Results
            
        End If
    End If
End Sub