Greetings,
I am trying to use Application Match but my code is not working. I get a run-time error '438 - object doesn't support this property or method.
Debug lands on "y = Application.Match(ST1.Cells(x, 1).ST2.Cells("A:A"), 0)" line of code.
Please Login or Register to view this content.
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
shg,
Thank you much for your responce.
Now I am getting a Run-time error5 on same line of code.
Change Cells to Range for ST2
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
I changed the line of code as you both suggested. However, the information from ST1 is not being copied to ST2.
How aboutPlease Login or Register to view this content.
Fluff13,
Thank you much!. Still not working. Let me work with it a little longer.
I am attaching a sample workbook for clarification.
Can you please explain in words, what you are trying to?
Does this do what you want?
Please Login or Register to view this content.
Fluff13,
I want to copy cell values from worksheet to worksheet.
Ex:
Worksheet "to" A:A contains unique numbers. Starting with row 3.
Worksheet "from" A:A contains unique numbers. Starting with row 2. Copy the cell values B:B and C:C to worksheet "to" matching the unique numbers within A:A.
shg,
Thank you much! I just noticed my sample workbook is incorrect. As well as my explanation in my previous post. *Working too many hours!!!
The columns on worksheet "to" are not next to each other. Columns O and Columns P are the columns the data needs to be copied to.
How about just showing an example of the desired result?
Okay see attached.
Please Login or Register to view this content.
Hi ! Try this !
According to the attachment (but cell to!A2 must be empty) an easy Excel starter demonstration :
PHP Code:Sub Demo1()
Const S = ",FALSE),"""")"
Dim F$
F = "=IFERROR(VLOOKUP($A3," & Sheet16.[A1].CurrentRegion.Address(External:=True) & ","
With Sheet15.[A3].CurrentRegion.Offset(, 14).Resize(, 2)
.Formula = Array(F & "2" & S, F & "3" & S)
.Formula = .Value2
.Columns(5).Formula = F & "5" & S
.Columns(5).Formula = .Columns(5).Value2
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 01-10-2020 at 07:17 PM. Reason: optimization …
Marc L,
WOW!!!! The code you provided has an incredible speed!!!
I am needing to modify the code a bit. If I run into an issue, I will reach out.
Again, thank you much!!!
shg,
Thank you as well!
Thanks for the rep' !
My demo just respects the TEBV main rule : Think Excel Before VBA ! Here just an easy Excel Basics formula …
Marc L,
You have provided direction for something I was not aware of!!! I have spent most of the night working with your code. Is it possible to keep the cell format from the original worksheet (Sheet16)?
According to your last attachment there is no cell format issue on my side …
As this procedure uses an Excel formula so the original cell format is retained but you can amend the format even by code.
Marc L,
I have revised the workbook. I want to keep the cell format on "from" worksheet.
Last edited by brentjohn; 01-11-2020 at 10:44 AM.
Sorry I posted the wrong workbook in my previous post. Please see the attached.
'cause of Item#4 the Range.Copy method keeps the cell format as well (like shg did in his code) :
PHP Code:Sub Demo2()
Dim V, Rg As Range, W
V = Sheet16.[A1].CurrentRegion.Columns(1).Value2
Application.ScreenUpdating = False
For Each Rg In Sheet15.[A3].CurrentRegion
W = Application.Match(Rg.Value2, V, 0)
If IsNumeric(W) Then
Sheet16.Rows(W).Columns("B:C").Copy Rg(1, 15)
Sheet16.Cells(W, 5).Copy Rg(1, 19)
End If
Next
Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Marc L,
Thank you much!!!!! Works great!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks