show a cell

s7y · #16 05-24-2012, 07:30 AM

ah ok... tested and it works... but... I get the column. what I need to get is the name of the cell that appears above or under the destination cells....

macropod · #17 05-24-2012, 04:54 PM

I've already shown you how to get the address of the 'destination' cell. As for wanting the "the name of the cell that appears above or under the destination", which is it? And what do you mean by 'name'?

s7y · #18 05-25-2012, 02:44 AM

Paul, the "Report" sheet shows the destination cells. All column of 4 cells have a highlighted cell above or under. They are all numbered. Row 2 in "Data" is in column D12

15 of Report. The address of D12

15 is 4 (D11). Row 4 in "Data" is in column I16:I19 of Report. The address of I16:I19 is 24 (I20). Of course any Row in Data can be placed anywhere in Report. That is why I cannot use a normal lookup formula

macropod · #19 05-25-2012, 03:02 AM

Let me see if I understand this. Consider column I on your report sheet. The macro matches against 9821 in I16. So, what do you then want it to find - the highlighted '9' in I11 or the '24' in I20, or both?

s7y · #20 05-25-2012, 03:34 AM

In this case "24".

macropod · #21 05-25-2012, 05:48 AM

Try:

Code:

Sub GetColRef()
Dim i As Long, j As Long, k As Long, Rng As Range
With ThisWorkbook.Worksheets("Data")
  j = .Range("A" & .Rows.Count).End(xlUp).Row
  For i = 2 To j
    Set Rng = ThisWorkbook.Worksheets("Report").Cells.Find(what:=Right(.Cells(i, 2).Value, 8), LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    If Rng Is Nothing Then
      .Cells(i, 5).Value = ""
    Else
      For k = 1 To i
        If Rng.Offset(k, 0).Interior.ColorIndex = 6 Then
          .Cells(i, 5).Value = Rng.Offset(k, 0).Value
          Exit For
        ElseIf Rng.Offset(-k, 0).Interior.ColorIndex = 6 Then
          .Cells(i, 5).Value = Rng.Offset(-k, 0).Value
          Exit For
        End If
      Next
    End If
  Next
End With
End Sub

s7y · #22 05-25-2012, 12:32 PM

Hello Paul, When I run the macro in the test sheet it works great, when I put it in mine and change some of the data (sheet names, destination cells) it will not work. But ok, what I see is that the result I am getting is the correct one. One question... in my original sheet there are no colors. Is it possible to get the same macro to run but "not on colors"?

macropod · #23 05-25-2012, 03:32 PM

The macro relies on the colours, because that is what you have provided as your example.

I don't know how you expect someone to provide a solution when the examples you provide aren't real. First you tell me the solution doesn't work because you forgot to mention that there are differences between the data in the data sheet and the report sheet. Now you tell me the solution doesn't work because the colours in your example sheet don't exist in the 'real' one.

I'm not going to waste any more time on this trying to provide solutions based on dodgy examples.

s7y · #24 05-25-2012, 03:39 PM

sorry about that Paul, I colored the cells to show which ones were the ones to be considered. I didn't think that that would create confusion.

macropod · #25 05-25-2012, 04:51 PM

Until you post some sample data that is truly representative of what you need a solution for, I'm not doing any more with this. You also need to explain whether, for example, the value you're looking for is always a certain number of rows above or below the matched value (eg 1 row above or 4 rows below) and how one can be sure that whatever's found there isn't something else.

s7y · #26 05-26-2012, 05:55 AM

Ok, here we go then. What I am working at is a loadplan for an airplane. Namely the Boeing 747-400 Freighter. Data is the sheet where I input the Unit Load Devices (ULD) coming from the warehouse. This is the list of all that will have to be loaded inside the plane. Report is the sheet that I will distribute to the loading team. This shows where the ULD have to be loaded. Basically this is the result of Weight and Balance calculations. Back to the sheets now: Data: Column A=weights, B=serial of ULD (there are no doubles), C=destination of Uld, D=Height of ULD and E=Specials into ULD (if any). I get the data via email and I insert it in Data by hand. Then I choose the positions where I want the ULD to be loaded (Report). The conditions are Weight and Height. We have different Heights: Q6, Q7, S6, S7, A1, A2, T, PLD and W2. PLD can go anywhere in the airplane. W2 can fit only below Row 19. Q6 can go in any position but the lowerdeck (not below row 19), Q7 can not be placed anywhere below Row 19 nor before column F, A1 and A2 same as Q6, but they are specially built to fit in nose positions (column A and B). T is the same as Q7 but it is specially built for tail position (Column R, becaue of max weight allowance). S6 is the same as Q6 but it is a slightly longer pallet, S7 is same as Q7, slightly longer pallets. For S6 and S7 also position restrictions applies but I have to check for the exact a/c registration. For the weights I can do conditional formatting and get an error message when ULD is too heavy for that position.

Back to the first problem. When I make a loadplan (report) I select the ULD in Data by mean of a macro and position it in Report. When I go back to Data to select the next one it would be very handy if the chosen position are shown and if it would be impossible to select an ULD if this has already been positioned. The workbook (S7y) that I have attached some posts ago is the actual layout of both Report and Data (the colors are only to show the positions, they are not there in the actual sheet so feel free to cancel them all).

Hope this makes more sense but please feel free to ask questions and, if you need to, I can send you the original program I am working on (I don't feel comfortable posting it here as there is some "sensitive" data in it).

Thank you very much and sorry for the trouble

macropod · #27 05-26-2012, 08:00 PM

Hi S7y,

Thanks for the explanation, but it's not really what I need. I need a workbook that is truly representative. It doesn't matter whether the data are real or fake - so long as they depict the structure of the real workbook and the kind of data it contains.

If I understand your latest post correctly, the workbook structure does represent the real one's layout, just not the kind of data or the shading. In that case, try:

Code:

Sub GetColRef()
Application.ScreenUpdating = False
Dim i As Long, j As Long, Rng As Range
With ThisWorkbook.Worksheets("Data")
  j = .Range("A" & .Rows.Count).End(xlUp).Row
  For i = 2 To j
    .Cells(i, 5).Value = ""
    Set Rng = ThisWorkbook.Worksheets("Report").Cells.Find(what:=Right(.Cells(i, 2).Value, 8), LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    If IsNumeric(Rng.Offset(-1, 0).Value) Then
      If Rng.Offset(-1, 0).Value > 0 And Rng.Offset(-1, 0).Value < 100 Then
        .Cells(i, 5).Value = Rng.Offset(-1, 0).Value
      End If
    End If
    If IsNumeric(Rng.Offset(4, 0).Value) Then
      If Rng.Offset(4, 0).Value > 0 And Rng.Offset(4, 0).Value < 100 Then
        .Cells(i, 5).Value = Rng.Offset(4, 0).Value
      End If
    End If
  Next
End With
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can I create a formula that will show result in the same cell and let me copy it down	CranstC	Excel	1	02-11-2012 01:29 AM
How can I delete the content of a cell in column if the cell value is more than 1000?	Learner7	Excel	2	06-27-2011 05:44 AM
Question: Hyperlink to other show, but close the current show	habibfikri	PowerPoint	0	12-26-2010 02:37 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM