Find Data that is not cell Dependent

**Thrux** · 04-27-2011, 02:09 AM

Hello,

I have made many improvements utilizing this site. I am now in a deadline and do not have weeks to play around like my last few projects. Attached is a sample of the workbook and request follow:

1. I am importing data with thousands of lines. This data is not always in the same order; some may be added/removed.
2. I need to find specific values within these lines and display on another sheet.
3. There are tags that can be used.

This will save me hours of paper work. If someone could point me in the right direction I think I could figure it out from there. I attempted to record my own; but if I added a row the values were wrong. Any help would be much appreciated. Also, I have already a compare workbook macro that will highlight the differences that will be used after the refresh of values on main sheet.

Regards,

**johncassell** · 04-27-2011, 11:22 AM

Hi, I'm a bit confused by this request..

You say 'everything hilighted should be transferred to the other sheet'. Seeing as every hilighted row has the same information tag (SeamPointData), how can we tell where the data from each row is supposed to land on the other sheet.

Thanks
John

**Thrux** · 04-27-2011, 02:11 PM

It is confusing, I know from another program that these are the x,y,z and rotation coordinates. SeamPointData is contained in many Levels of the program. I need to identify it correctly by at least two specific's first. I think I figured out a way to achieve my request; although it may be abstract.

I will highlight column B and search for Lambda Sta1, Then Search for WF6. Use relative reference and scroll left to column A. Search for "Name" ie. SeamPower. I may then Copy and Paste special (Values) to the other sheet. This is the only way I can ensure I am getting the correct data since it may be on a different row week to week. I did record a macro and attached the file for review. The only data being pasted are cells E18,N18.

Thank you for the reply.

**Thrux** · 04-28-2011, 02:11 AM

Hello again,

I have completed one sheet. A copy is attached for review if interested. Now I just need to update the remaining 11sheets. I think I may be able to just edit the code to different names.

**johncassell** · 04-28-2011, 08:52 AM

will all the values always be relative to the cell LAMBDA STA1?

I.e If LAMBDA STA1 is in cell B140 then the SeamPower value of 3600 will be in cell B155 (15 rows away from SeamPower) ?

**Thrux** · 04-28-2011, 12:59 PM

All values will be relative to Labda Sta1 for that sheet. Although my worries are Lambda Sta1 may be on B145 with next import of data. Also SeamPower may be B160 on the next import of data.

I recorded macros that will:

1. Search Column B for Lambda Sta1 on sheet .rlpimport
2. Search for "WF" depending on what is needed. I.e WF6 on sheet .rlp import
3. Activate relative references and scroll left to Column A on sheet .rlp import
4. Search for SeamPower and then scroll right to value. on .rlp sheet
5. Copy value, turn off relative references.
6. paste special (value) to the proper cell on sheet Fix25.

Sub WF_13()
'
' WF_13 Macro
'

'
    Sheets(".rlp Import").Select
    Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Cells.Find(What:="seampower", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("E23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "'Weld Schedule log.xlsm'!Start_At_Top"
    Sheets(".rlp Import").Select
    Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Cells.Find(What:="seamvelocity", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("F23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "'Weld Schedule log.xlsm'!Start_At_Top"
    Sheets(".rlp Import").Select
    Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Cells.Find(What:="seamtime", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("G23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "'Weld Schedule log.xlsm'!Start_At_Top"
    Sheets(".rlp Import").Select
    Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Cells.Find(What:="seampointdata", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 5).Range("A1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("H23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "'Weld Schedule log.xlsm'!Start_At_Top"
    Sheets(".rlp Import").Select
    Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Cells.Find(What:="seamcoord", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1:F1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("I23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "'Weld Schedule log.xlsm'!Start_At_Top"
End Sub

**Thrux** · 04-28-2011, 01:18 PM

Another problem, I have 24 macros for on sheet of data. I need to update 12 sheets of data. This will be a total of 288 macros! Will excel be able to perform this? Currently the screen flickers for about a minute for one sheet. This is very exciting learning as I go. I am going to search later tonight on another way to get this data or remove the flicker.

**johncassell** · 05-03-2011, 08:36 AM

Sorry for late reply on this. Ill look at the main thing shortly but to remove the flicker you just put this at the start of the code

application.screenupdating = false

and then put this at the end of the code

application.screenupdating = true

**johncassell** · 05-03-2011, 09:15 AM

Your code seems to be doing unnecessary things..

 Cells.Find(What:="lambda sta1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="wf13", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

all that serves to do is find lambda sta1 and then find wf13, if you're not doing anything in between those two 'finds' then the first one is pointless.

You are also copying and pasting values which is unecessary. For example in this code...

  Cells.Find(What:="seampower", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Sheets("Fixt 25 Yag").Select
    Range("E23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

You are (a) finding seampower and then (b) moving to the next cell to the right then (c) copying the cell value and then (d) pasting it into cell E23 on the Fixt sheet.

This could all be done with the following code...

'Find seampower...
  Cells.Find(What:="seampower", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
'Put the value of the next cell into cell E23 on the Fixt sheet...
Sheets("Fixt 25").range("E23").value = activecell.offset(0,1).value

could you try to tidy your code up with my suggestion above and also post what code is in this macro..

Weld Schedule log.xlsm'!Start_At_Top

as this may have unnecessary stuff in it as well.

Thanks
John

**Thrux** · 05-03-2011, 03:04 PM

Thank You John for the reply. I will attempt to explain this the best I can since I cannot attach due to size limit.

Within sheet .rlp Import there are 3 programs each containing 4 stations; and each station contains 22 points with seampower, velocity, coordinates etc. These labels will not be on the same row each time this sheet is updated.

I utilized the find function to locate station 1 then use relative references to move left to labels. Then I would find label needed I.e. seamppower and copy setting value. I did not want to pull data for station 2 by just searching seam data.

I do like

'Put the value of the next cell into cell E23 on the Fixt sheet...
Sheets("Fixt 25").range("E23").value = activecell.offset(0,1).value

And will attempt to use it.

I attempted to keep my Modules in order as routine and sub routines. This is the most I have used this and I think I made it to complex. If you would still like to review let me know.