show a cell

s7y · #1 05-20-2012, 08:52 AM

hello there, I know that the title is not very self explanatory but I really don't know how to explain what I need in 2 words. Attached you will find a file that can help understand what I need. I have this sheet (Data) where I have data inserted horizontally. I then copy this data vertically into the Results. What I need to achieve is that when data is inserted into Reports, the column name (highlighted) is shown in column F in Data. As you can see some highlighted cells are on top of the columns and some others at the bottom (all columns are of 4 cells).

Thanks for any help

macropod · #2 05-20-2012, 09:38 PM

There are some variable here that need to be defined/taken account of before you'll be able to get reliable results, including:
• do any of the columns on the Data sheet have contents that are unique, so that there can be no mistake as to which columns on the Report sheet are being matched?
• what is the basis, if any, for rows and columns on the Report sheet increasing for each record?
• what process are you using to populate the Report & Data sheets?

s7y · #3 05-21-2012, 06:49 AM

Howdy Paul, in reply to your questions:

. cells in column B of Data are unique
. numbers and letters are random, there is no mathematical increase or anything like that
. Data sheet is manually populated. Then by mean of macros the rows are copied and pasted into Report . Attached please find the sheets as they look like in "real life" (I have left the macros in this workbook for you to see).

As you can see in Report the cells have been placed but now I need to see their positions in Data. Also, please note that once the cells are in position it might be necessary to move them to their final destination. This will happen, again, by using macros that will run directly from "Report" (in report select the cells you want to swap with ctrl and then push ctrl+s).

Hope this helps

Thank you very much

macropod · #4 05-21-2012, 06:18 PM

You could use something like:

Code:

Sub GetAddresses()
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
    Set Rng = ThisWorkbook.Worksheets("Report").Cells.Find(what:=.Cells(i, 2).Value, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    If Rng Is Nothing Then
      .Cells(i, 5).Value = ""
    Else
      .Cells(i, 5).Value = Split(Rng.Address, "$")(1)
    End If
  Next
End With
End Sub

If you want the address of the matched cell, change 'Split(Rng.Address, "$")(1)' to 'Rng.Address'.

s7y · #5 05-22-2012, 12:44 PM

Hi there Paul, can you please tell me how to use that? I have copied it into my module1 and changed the names of DATA and REPORT to the actual names but nothing happens

macropod · #6 05-22-2012, 02:44 PM

Have you run the macro (Alt-F8, 'GetAddresses', OK)? The column names should then appear in column E of your data sheet.

s7y · #7 05-22-2012, 11:00 PM

I did run the macro but nothing appears in E (well, I have changed it into F... but still....).

macropod · #8 05-22-2012, 11:03 PM

That implies some difference between what's in Column B on the data sheet and the correspinding entry on the report sheet. As coded, even an extra space character will void the match.

s7y · #9 05-22-2012, 11:30 PM

oh... ehmmm... yes... did I forget to mention that there is some difference between what's in Colum B on the data sheet and the corresponding entry on the report sheet?... On the report sheet only the last 8 characters will appear. The first 3 are automatically cancelled... is it possible to make it so that the 5 central digits in column B are checked?

s7y · #10 05-22-2012, 11:38 PM

Paul, I checked the macro by inserting the complete column B value. The result is the name of the column where the value is.

macropod · #11 05-22-2012, 11:40 PM

That's a fairly major detail to leave out!!

Your specs are now totally confusing. You say "On the report sheet only the last 8 characters will appear". So how is that consistent with "the 5 central digits in column B are checked"? Which has the greater number of characters - the report sheet or the data sheet?

s7y · #12 05-22-2012, 11:51 PM

Data sheet has the greater number of characters (10). In report I only show the last 8. The first 5 (in report that is) are unique, the last 2 will repeat most of the time

macropod · #13 05-23-2012, 12:55 AM

In that case, it shouldn't matter that the last two characters may appear in more than one location (there is still one character not accounted for); what matters is that the overall string being searched for is unique.

Try replacing '.Cells(i, 2).Value' with 'Right(.Cells(i, 2).Value,8)'

s7y · #14 05-23-2012, 08:57 AM

Hello again Paul. I have tried the code but when I replace it with the last one you sent I get a run-time error (424 - object required) and this line is highlited:

Set Rng = ThisWorkbook.Worksheets("Results").Cells.Find(what :=Right(.Cells(i, 2).Value, 7).Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

Also, when I use the old version of the code, the result I get is the name of the column and not the name of the cells just above the results....

macropod · #15 05-23-2012, 03:46 PM

What I said you should end up with is:
Right(.Cells(i, 2).Value,8)
What you've got is:
Right(.Cells(i, 2).Value, 7).Value
Can you see the difference?

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