PDA

View Full Version : Solved: Find Value in Column And Paste data into it



Hamond
05-06-2009, 03:46 PM
Hi,

I have the macro below I created that copies a block of cells (m50:o70)from a worksheet. However I am struggling with the rest of the code in terms of telling it where to paste the data.

I want to use the value from the first cell of this block (m50) and find it in column A of each sheet and paste the data where this entry occurs.

For example if the value in m50 in the source sheet is 15-April-2009, I want to find the same value in column A (it will exist!) of a given sheet and paste the data block starting at that cell. All sheets will have this value in column A but the row it it resides in the column will differ sheet to sheet hence I need to use some kind of find function.

Sub Update()
worksheets("Data").select
Range(m50:O70).Copy
"find value in first cell (m50) and paste block of data in matching cells in colunm A of all sheets.
End Sub
And I would like to exclude the following sheets from the procedure: Current Quotes, test

Would this be easy to do?

Thanks,

Hamond

Simon Lloyd
05-06-2009, 05:28 PM
Hi,

I have the macro below I created that copies a block of cells (m50:o70)from a worksheet. However I am struggling with the rest of the code in terms of telling it where to paste the data.

I want to use the value from the first cell of this block (m50) and find it in column A of each sheet and paste the data where this entry occurs.

For example if the value in m50 in the source sheet is 15-April-2009, I want to find the same value in column A (it will exist!) of a given sheet and paste the data block starting at that cell. All sheets will have this value in column A but the row it it resides in the column will differ sheet to sheet hence I need to use some kind of find function.


Sub Update()
worksheets("Data").select
Range(m50:O70).Copy
'"find value in first cell (m50) and paste block of data in matching cells in 'colunm A of all sheets.
End Sub
And I would like to exclude the following sheets from the procedure: Current Quotes, test

Would this be easy to do?

Thanks,

HamondIm not entirely sure what your after but try this:
Sub Update()
Dim Sh As Worksheet, Rng As Range
Worksheets("Data").Range("m50:O70").Copy
For Each Sh In Sheets
If Sh.Name = "Current Quotes" Or Sh.Name = "Test" Then GoTo Nxt
With Sh.UsedRange
Set Rng = .Cells.Find(What:=Worksheets("Data").Range("m50").Value, After:=.Range("A1"), LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Range(Rng.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Nxt:
Next Sh
End Sub

Hamond
05-07-2009, 06:33 AM
Hi,

I tried running the macro but get a Run time error 91: Object Variable or with Block variable not set.

Debuging takes me to the following lines in the code:

Range(Rng.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Also I noticed the end with command was missing in the code so I added this in, hope it is correct.


Sub Update()
Dim Sh As Worksheet, Rng As Range
Worksheets("Data").Range("m50:O70").Copy
For Each Sh In Sheets
If Sh.Name = "Current Quotes" Or Sh.Name = "Test" Then GoTo Nxt
With Sh.UsedRange
Set Rng = .Cells.Find(What:=Worksheets("Data").Range("m50").Value, After:=.Range("A1"), LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Range(Rng.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
Nxt: Next Sh

End Sub

Bob Phillips
05-07-2009, 06:58 AM
Try this amendment



Sub Update()
Dim Sh As Worksheet, Rng As Range
Worksheets("Data").Range("M50:O70").Copy
For Each Sh In Sheets

If Sh.Name <> "Current Quotes" And Sh.Name <> "Test" Then

With Sh.UsedRange

Set Rng = Nothing
Set Rng = .Columns(1).Find( _
What:=Worksheets("Data").Range("M50").Value, _
After:=.Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then _
Rng.PasteSpecial Paste:=xlPasteValues
End With
End If
Next Sh
Application.CutCopyMode = False
End Sub

Simon Lloyd
05-07-2009, 08:16 AM
Hi,

I tried running the macro but get a Run time error 91: Object Variable or with Block variable not set.

Debuging takes me to the following lines in the code:

Range(Rng.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Also I noticed the end with command was missing in the code so I added this in, hope it is correct.


Sub Update()
Dim Sh As Worksheet, Rng As Range
Worksheets("Data").Range("m50:O70").Copy
For Each Sh In Sheets
If Sh.Name = "Current Quotes" Or Sh.Name = "Test" Then GoTo Nxt
With Sh.UsedRange
Set Rng = .Cells.Find(What:=Worksheets("Data").Range("m50").Value, After:=.Range("A1"), LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Range(Rng.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
Nxt: Next Sh


End SubApologies, it was late last night when i constructed it, i didn't test it, it probably failed because of the end with missing, however xld's solution is a bit smarter :)

Bob Phillips
05-07-2009, 08:33 AM
Yeah, but you saved me the footwork!

I think cancelling the copy in the loop didn't help either :)

Simon Lloyd
05-07-2009, 09:49 AM
Yeah, but you saved me the footwork!

I think cancelling the copy in the loop didn't help either :)Lol, i didnt even see that, i had very little sleep yesterday and then had to work all night i was doing anything to try and keep awake....obviously i wasn't that awake!! :)

Hamond
05-10-2009, 03:48 PM
Hi,

I tried running the code by XLD and although I don't get any errors it isnt pasting any data into the individual sheets and I just can't work out why. It does like it copies the range from the datasheet as I can see this highlighted but doesn't paste the data for some reason. I've checked to see that the date in M50 exists in column A of each sheet and it appears that it does.

Any ideas why this might not be working?

Thanks,

Hamond

GTO
05-10-2009, 07:17 PM
Greetings Hammond,

I cannot say I have a real tight grasp on the "whys" of this, but I found:

I ran into the same thing, that is, the date was not being found and hence, no paste. So, I manually selected an appropriate sheet, selected entire Col A, and Edit|Find 4/15/09 (I don't believe the m/dd vs dd/mmm should matter).

Executing the Find resulted in the "Sorry buddy, it ain't there" msg. However, changing it to look in Formulas fixed. (I looked in vba and regular help, I find nothing descibing what is being looked for when using xlFormulas vs xlValues. Bob, Simon, Anyone feel like explaining these?)

With this tweak, I stepped thru the code and discovered that for whatever reason, 'With Sh.UsedRange' seems to "override" the '.Columns(1).', as when stepping thru (I happen to have the sheet 'Data' first/leftmost) the code, the first range it would Find was M50 on 'Data'.

Anyways Hammond, please forgive the extra details, but I'm hoping for some clarity in xlFormulas vs xlValues. Here is Bob's code with tweaks included (hopefully okay):


Sub Update()
Dim Sh As Worksheet, Rng As Range
Worksheets("Data").Range("M50:O70").Copy

For Each Sh In ThisWorkbook.Worksheets

If Sh.Name <> "Current Quotes" And Sh.Name <> "test" Then

With Sh

Set Rng = Nothing
Set Rng = .Columns(1).Find( _
What:=Worksheets("Data").Range("M50").Value, _
After:=.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
'Debug.Print Rng.Address(False, False)
Rng.PasteSpecial Paste:=xlPasteValues
End If
End With
End If
Next Sh
Application.CutCopyMode = False
End Sub


Hope this helps,

Mark

Edit: ACK! I forgot to mention that if you don't want the sheet 'Data' to be searched, you could tack in 'And Sh.Name <> "Data"' in the IF test.

Hamond
05-11-2009, 08:16 AM
Hi Mark,

Thanks for the ammendments. I can confirm now this works. Hope you get the answers to the question your looking for. I did a find manually and recorded it to see what the macro shows and it was xlValues in the code so still not sure why this didn't work.

Anyway, I've just realised that in order to get this process to work most efficiently I need to modify the code slightly, instead of pasting the data to all the sheets I only want to paste it to one sheet in the workbook depending on the cell value in N1 in datasheet, i.e I will store the sheet name in N1.

So for example if the value in N1 of the sheet data is "V-MA", I want to only paste the data to sheet called V-MA. Everything else is the same, it's just I want to past to one sheet whoose sheetname is in cell N1.

Would it be quick to ammend the code to achieve this. I'm assuming I just need to remove a few lines and add one two lines in?

Thanks,

Hamond

GTO
05-11-2009, 04:09 PM
...I can confirm now this works...

...I did a find manually and recorded it to see what the macro shows and it was xlValues in the code so still not sure why this didn't work...


Hi Hammond,

While I am glad its working, that second part (which I tried and had the same results) has me pretty much convinced I may have taken you a step backwards rather than forwards. If this is the case, you have my apologies.

@XLD:

...ahem...uhm, excuse me, but I think I that rather than a possible 'good spot', I may well have goobered up your code from #4. (Sorry, my bad.) Would you shed some light on what .Find is doing with xlValues vs xlFormulas and/or tricks/issues w/finding dates? Thank you so much Bob.

Mark

Simon Lloyd
05-11-2009, 11:54 PM
Hi GTO, if you'd like to search for something inside a cell,
perhaps the output of a formula, rather than the formula itself,
you'd use the xlValues constant.

If you'd like to search through the formulas and find a particular
variable, you'd use the xlFormulas constant to identify where to
search.

Take a look at Ron de Bruins site which has a date find...etc the code is annotated and some explanations. http://www.rondebruin.nl/find.htm

GTO
05-12-2009, 12:24 AM
Hi Simon,

Thank you very much:) I just finished printing it and will certainly study. I had found this: http://www.cpearson.com/excel/DateTimeVBA.htm by Chip Pearson, so leastwise I was feeling a little less like I had driven into a ditch...

Thanks again; for me its just way better to know why its working (or going Kaboom) than having it work but not a clue as to why...

Mark

Hamond
05-12-2009, 06:11 AM
Hi Guys,

As per my last post, I've attempted what I wanted myself which I've posted below. Unfortunatley when I run the code I get an "Object required" error. The code does select the right sheet based on what's in cell N1, however there seems to be an issue with pasting the data.

Debugging it takes me to the following part of the code:


Set rng = Nothing
Set rng = .Columns(1).Find( _
What:=Worksheets("Data").Range("M250").Value, _
After:=.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)

New bits of code I've indicated as new. Any ideas on what's missing?

Thanks,

Hamond


Sub Update() ' only update one sheet
Dim Sh As Worksheet, rng As Range
Dim x As String 'new
x = Worksheets("Data").Range("N1") 'new

Worksheets("Data").Range("M250:O300").Copy
Worksheets(x).Select 'new

With Worksheet 'new
Set rng = Nothing
Set rng = .Columns(1).Find( _
What:=Worksheets("Data").Range("M250").Value, _
After:=.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
'Debug.Print Rng.Address(False, False)
rng.PasteSpecial Paste:=xlPasteValues
End If
End With
Application.CutCopyMode = False
End Sub

GTO
05-12-2009, 09:11 AM
Greetings Hamond,

Apologies on mis-spelling you name previously.

With the info from Chip Pearson's site and Ron De Bruin's (Per Simon, and thanks again), I think this might answer your latest.


Sub UpdateMe()
Dim rngDateToFind As Range
Dim wksDestination As Worksheet
Dim wksSource As Worksheet

On Error Resume Next
Set wksSource = ThisWorkbook.Worksheets("Data")
Set wksDestination = ThisWorkbook.Worksheets( _
ThisWorkbook.Worksheets("Data"). _
Range("N1").Value)

Set rngDateToFind = wksDestination.Columns(1).Find( _
What:=DateValue(wksSource.Range("M50").Value), _
After:=wksSource.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext _
)
If Not Err.Number = 0 Then Exit Sub
On Error GoTo 0

wksSource.Range("M50:O70").Copy
rngDateToFind.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub


I did use the old M50:O70, so you'll need to adjust...

Mark

Simon Lloyd
05-13-2009, 03:24 AM
Hamond, when posting code please either click the VBA button in your post window then paste your code between the tags or highlight your code once posted then click the VBA button, this makes the code much easier to read :)

I've already done your previous posts for you.