#1
|
|||
|
|||
show a cell
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 |
#2
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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
|
#6
|
||||
|
||||
Have you run the macro (Alt-F8, 'GetAddresses', OK)? The column names should then appear in column E of your data sheet.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
I did run the macro but nothing appears in E (well, I have changed it into F... but still....).
|
#8
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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?
|
#10
|
|||
|
|||
Paul, I checked the macro by inserting the complete column B value. The result is the name of the column where the value is.
|
#11
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
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
|
#13
|
||||
|
||||
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)'
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
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.... |
#15
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |