Problems with Loop and Paste

[Legacy 316950]

Hello everyone. I wrote this code to copy spefic values from Sheet2 to Sheet1. But i don't know how to use loop properly in VBA, and i don't know how to paste these values.
Here is my code. I know it's not a very smart code, but i'm new on VBA, if anyone see a tip to give me, i will be very grateful.

Sub compare_copy_paste()

'Declaration of variables
Dim d1 As Range
Dim base  As Rangge
Dim copia As Range

'Range that i don't know how to use in the loop
Sheets("Sheet1").Select
Set d1 = Range("A6:A600")
d1.Select
Sheets("Sheet2").Select
Set base = Range("B2:B700")
base.Select

'Loop with problems, when the macro is running is like a infinite loop.
For i = 6 To 700
    For j = 1 To 700
        If Worksheets("Sheet1").Cells(i, 1).Value = Worksheets("Sheet2").Cells(i, 2) Then
           Worksheets("Sheet2").Cells(i, 11).Copy
           Worksheets("Sheet1").Select
            Worksheets("Sheet1").Cells(i, 5).Paste
        End If
    Next j
Next i

End Sub

 

[hiker95]

leonardotamashiro,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

For i = 6 To 700
    For j = 1 To 700

For each i, you are looping thru the j's 700 times.

There probably is a better way to search, but, we will have to see samples of both worksheets, and, what the results should look like.


To start off, and, so that we can get it right on the first try:

Can you post screenshots of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?


To post a small screen shot (NOT a graphic, or, picture) try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Legacy 316950]

Hi hiker95, thank for help me.
I am using excel 2013 and windows 8. Yes 700 times its to much for a loop, but i am still thinking how to write a code that give me the last line of each sheet.


These are the Screenshot of my workbook and the result that i'm tryng to achieve. If it's not a problem i sent the link of mydropbox.

https://www.dropbox.com/s/8c1usodbtrgjdds/Sheet1.png?dl=0

 

[hiker95]

leonardotamashiro,

These are the Screenshot of my workbook and the result that i'm tryng to achieve. If it's not a problem i sent the link of mydropbox.

https://www.dropbox.com/s/8c1usodbtr...heet1.png?dl=0

Your link is to PNG picture/graphic.

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


I will need to see your actual workbook with the two worksheets.


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[Legacy 316950]

Sorry, I make a mistake with the word screenshot.

Now I upload my workbook with my code

here is the link
https://app.box.com/s/pihp2a9ur15ks5r6jvxsqrjtyh8dv9ex

I am trying to compare each cell of the Range("A6"&lastrow) of Sheet1, with the Range("B2"&lastrow) of Sheet4 and copy values from sheet4 if they are equal then the next step is to paste in the Sheet1 the values that i copied.

Thanks for the attention.

 

[hiker95]

leonardotamashiro,

I was having a problem with your workbook. Each time I would download it, two workbooks opened in Excel?????

So I copied worksheets Sheet1, and, Sheet4 into a new workbook.


Here are the results in worksheet Sheet1 (I have only shown those 28 rows that returned data from worksheet Sheet4) (macro runtime was 0.047 seconds):

Excel 2007
	A	F
1
2
3
4
5	Ponto de medio	MIDIA
6	MGUSCLBAR2-06P
20	SPABBRENTR101P	6
21	SPADBAENTR-01P	12
22	SPADBAENTR-01R	12
27	SPALPTENTR101P	5
28	SPALPTENTR101R	4
32	SPALPTENTR303R	4
68	SPBARRENTR202P	6
69	SPBARRENTR202R	5
74	SPBAU-BAUC208P	6
107	SPBUCKENTR101R	31
162	SPDOC-ENTR-01R	3
179	SPFDZ1ENTR101P	4
262	SPMGB-INTFA02P	3
263	SPMGB-INTFA02R	3
266	SPMGB-TR2--01P	3
267	SPMGB-TR2--01R	3
272	SPMIV-DFRM-05P	7
380	SPRSA2ENTR101P	19
382	SPRSJRENTR-01P	9
383	SPRSJRENTR-01R	8
407	SPSCA2ENTR202R	6
412	SPSGBFENTR101P	10
413	SPSGBFENTR101R	10
472	SPTDBRENTR101P	7
473	SPTDBRENTR101R	7
481	SPTEL8ENTR-01P	6
482	SPTEL8ENTR-01R	6
485	SPTIBUENTR-01R	9
522	SPYQA-ENTR-01R
523
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CompareCopyV2()
' hiker95, 02/17/2015, ME836664
Dim w1 As Worksheet, w4 As Worksheet
Dim c As Range, b As Range
Set w1 = Sheets("Sheet1")
Set w4 = Sheets("Sheet4")
With w1
  .Activate
  For Each c In .Range("A6", .Range("A" & Rows.Count).End(xlUp))
    Set b = w4.Columns(2).Find(c.Value, LookAt:=xlWhole)
    If Not b Is Nothing Then
      .Cells(c.Row, 6).Value = w4.Cells(b.Row, 11).Value
    End If
  Next c
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CompareCopyV2 macro.

 

[Legacy 316950]

Hiker95,

I am really grateful for your help. I will study this "for each" that i don't know how to use yet and it's much more elegant than one "for" inside other "for" that i was used. I will try your code adn if i change something i post my code.

thank very much.

 

[hiker95]

leonardotamashiro,

Thanks for the feedback.

You are very welcome. Glad I could help.

It sounds like you are trying to learn VBA?

See if something, in the below link, of my most up to date list will help you:

Training / Books / Sites as of 1/17/2015

http://www.mrexcel.com/forum/excel-questions/829893-macros.html#post4048295


And, come back anytime.