Copy > 1 Value from Column & Paste (1 Viewer)

[noboffinme]

Hi

I have a workbook with 2 worksheets, wksht1 & wksht2.

'wksht1' has column "A" which contains numbers (Long) with duplicates.

'wksht2' has the summarised column of numbers from wksht1, no duplicates.

I want to select a number in wksht1, find the value (String) that is 10 Columns to the right & hold that value.

The code moves up the column, finds the same number & value 10 columns to the right & holds that value.

When the are no more matches to that number in the column in wksht1, the values are pasted into wksht2 on the row where the number matches.

I'm mainly having issues with holding all the values for the same number in the column on wksht1 before I move over to paste the values onto the new sheet.

Can anyone assist?

 

[Trevor G]

Can you show a sample of the data on Sheet1 without the option added then show another sample of what you would expect to see once you run through your process.

Are you wanting a forumla or VBA as well?

Would be handy to know which Excel Version you are using.

 

[Brianwarnock]

Why hold the values?
Just paste them into wksht2 as you find them, all you have to do is move along the wksht2 row.

Brian

 

[noboffinme]

Thanks Trevor G

I will need a macro to do this, I've included an example of what I need, it works up to the point of copying the Plan name opposite the Number & going over to the 2nd sheet, finding that number & pasting the plan name in an adjacent column.

Hi Brian,

I thought it would be faster to collect the plan names per number & only need to go over to the 2nd sheet once per number rather than several times - to speed up the macro.

I can get the first plan value & paste this into the 2nd wksht under the 'Plan1' column, but I'm stuck at going back to the 'RAW_DATA' wksht to find the next occurence of the number to get the next plan name.

Please have a look to see where I'm up to, Cheers

 

[Brianwarnock]

i don't have 2007

 

[noboffinme]

Here's the same file in 2003, Cheers

 

[noboffinme]

Hi

In case anyone needs to do this as described above, I have the answer, in order to save the 'found' value for pasting later & avoid the error codes, the code is as follows - working example attached;

Firstly - To activate the 'Found' cell;

NOTE - 'acno' is the variable name
------------------------------------------------------------
Selection.Find(What:=acno, after:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Next - To hold the value for pasting onto the second spreadsheet;

plan1 = ActiveCell.Cells.Offset(0, f).Value
'------------------------------------------------------------

 

[Brianwarnock]

I am not getting on here much at the moment but did look at your spreadsheet this morning and it appeared to bear no resemblance to your original post.

I thought that you wanted to do something like the following rough code which when a value is selected on sheet 1 scans down sheet2 for all equal values and iterates along the row in sheet1 setting each cell equal to a value in sheet2 removed 4 cols

I couldn't actually find the sheets you mentioned.

Brian

lastrow2 = Sheets("sheet2").UsedRange.Cells(1, 1).Row + Sheets("sheet1").UsedRange.Rows.Count - 1

With ActiveSheet.UsedRange
lastrow1 = .Cells(1, 1).Row + .Rows.Count - 1
End With
ActiveCell.Select
srch = Selection.Value
arow = Selection.Row
acol = 2

currentrowsht2 = 2

Do Until currentrowsht2 > lastrow2
   If Sheets("sheet2").Cells(currentrowsht2, 1) = srch Then
   Sheets("sheet1").Cells(arow, acol) = Sheets("sheet2").Cells(currentrowsht2, 4)
   acol = acol + 1
   End If
currentrowsht2 = currentrowsht2 + 1

Loop

 

[noboffinme]

Hi Brian

Apologies I still didn't attach a 2003 version for you once I'd fixed this.

The file will explain things better.

As I wanted to hold an a/c no from the 'PIVOT_SUMMARY' wksht >> go to the 'RAW_DATA' wksht >> find all the same a/c nos >> save the value that is in column 'f' for those same a/c nos >> & finally add the variable(s) value back onto the 'PIVOT_SUMMARY' worksheet on the same row as the a/c no in subsequent columns.

To start with, I recorded some find & find next code to achieve this & one error was to change the 'Activate' in the below code part to 'Select'

Selection.Find(What:=acno, after:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).[B]Select
[/B]

My code to try & hold the variable value was eventually corrected to the below.
I found the below will hold the value on that row in column 'f' (f is an Integer) & I repeated this for any other matches on the 'RAW_DATA' wksht - so the solution was the below;

plan1 = ActiveCell.Cells.Offset(0, f).Value

After this I just went to the 'PIVOT_SUMMARY' sheet & added those values.

I thought a better solution would be to hold all the column 'f values in an array to hold all at once rather than get them one by one as my code does, but I'm not that experienced with arrays.

I'll have a look at you code to see if I can improve what I've done, Thanks

 

[Brianwarnock]

Ok I see what you are doing it was a faily simple missunderstanding of your names in the original post.
I haven't included the outer loop in my code, BTW yours should stop at row 5 not 2.

Also Count is not used and you do not need the Do loop as the code is written in line and never actually loops.

Brian

 

[Brianwarnock]

Took another look after breakfast and this is how I would do it , it allows for any number of Plans but you could build in a restriction.

Hope it helps.

Note that I do not use Find or Copy and Paste, nor do I Select the sheets as that really slows the performance.
If the data is huge sorting the 2 sheets on account and writing code to work your way through allowing for mismatches in both directions could be faster, I've done that when I was more adept at coding.

Cheers
Brian

Sub Bjwtest()

Dim lastrowRaw As Integer
Dim lastrowSum As Integer
Dim acno As String
Dim acol As Integer
Dim currentrowRaw As Integer
Dim i As Integer


lastrowRaw = Sheets("Raw_DATA").Range("A65536").End(xlUp).Row
lastrowSum = Sheets("PIVOT_SUMMARY").Range("A65536").End(xlUp).Row


For i = 5 To lastrowSum
 acno = Cells(i, "A").Value
 acol = 26

    currentrowRaw = 2

Do Until currentrowRaw > lastrowRaw
   If Sheets("RAW_DATA").Cells(currentrowRaw, "A") = acno Then
   Sheets("PIVOT_SUMMARY").Cells(i, acol) = Sheets("RAW_DATA").Cells(currentrowRaw, "C")
   acol = acol + 1
   End If
currentrowRaw = currentrowRaw + 1

Loop
Next i
End Sub

 

[noboffinme]

Thanks Brian

Your code is very efficient!

I'm surprised how fast that runs considering the code holds an A/c number variable & runs through the whole column of A/c numbers on the 'RAW_DATA' wksht each time to find a matching value.

I thought the less steps or 'Loops' the code went through , the faster the code would perform - that's why I thought rather than loop through all values, I could use Find & Find Next to go straight to the value I need & then find the Next etc ...

I usually test my code (once the bugs are fixed) by holding down the F8 button in the IDE & watching the Locals window values update & the spreadsheet to see the values change & additions made to the wksht.

I was really surprised that while I was running your code by holding down the F8 button in the IDE it took much longer to add the results to the spreadsheet however, when the code was run from the command button, the results were almost instant!

Can you advise why this code works so much faster with more loop steps?

Thanks again

Peter

 

[Brianwarnock]

runs through the whole column of A/c numbers on the 'RAW_DATA' wksht each time to find a matching value.

This is not true, I update the row number and start the search from there whether there is a hit or not. As I mentioned sorting both sheets and iterating through them might prove faster if you care to work the code.

Comparing my code with yours
Less function calls, no data movement ie no copy and paste, and no use of the SELECT, chergh an Excel expert who used to frequent this forum considered that referencing the worksheets with SELECT and ACTIVECELL etc always caused a big performance hit.

BTW why did you Dim acno as a string, I followed this incase there is a reason, surely it should be an integer?

Brian

 

[noboffinme]

Thanks again Brian

Please don't get the impression that I'm second guessing your code, I am amazed that it runs so fast & will now apply this logic in similar cases.

I just didn't think it would be faster so i didn't try that method.

Chergh has also assisted me in the past so I don't doubt the logic to run the code this way.

Also, yes I should have 'Dim' 'd the A/c no as Integer, but I was getting an error '# 6 - Overflow', tried it also on your code, same problem.

It happens on this line -

acno = Cells(i, "A").Value

Tried to fix by adding CInt before the brackets, but no luck, Cheers

 

[Brianwarnock]

Oh dear I'm getting careless in my old age, it can't be Integer it has to be Long because of the size of the numbers.
Also the lastrow needs to be

lastrowSum = Sheets("PIVOT_SUMMARY").Range("A65536").End(xlUp).Row - 1

or you will get a data mismatch, with String I spent time searching for acno = Grand Total


Cheers

Brian

 

[noboffinme]

Thanks Again Brian

I should point out that the 'Overflow' error occurs when I try your code on the real dataset rather than the dummy data file I uploaded onto the forum.

The difference is the a/c nos have 13 digits & there are approx 36,000 records in the real file.

So to overcome this, I tried defining 'acno' as Long instead of Integer but still got the same 'Overflow' error - it would however work if 'acno' was a string??

 

[Brianwarnock]

Ah! yes as we can see from Data Types in help Long only goes as far as

Long
(long integer) 4 bytes -2,147,483,648 to 2,147,483,647

but Single will work.

not sure if this is better than using String but I do prefer to define number fields as number fields.

Cheers

Brian