Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2016, 10:06 AM
cinstanl cinstanl is offline Using If function in VBA to match data from two sheets Windows 7 64bit Using If function in VBA to match data from two sheets Office 2016
Novice
Using If function in VBA to match data from two sheets
 
Join Date: Dec 2016
Posts: 3
cinstanl is on a distinguished road
Default Using If function in VBA to match data from two sheets

I have a workbook that I have already added code to, but I believe it is taking and doing a partial match... so if the data for example... 1584 on the Aging report.... matching....Inv: 10215849-RI on job costs.because of the middle numbers.. it is bringing the data over from JobCosts and placing it in V on the Aging... this is incorrect, the invoice numbers have to be an exact match... I have highlighted a few examples.... the ones in Yellow do not have a matching invoice number in column K on the JobCosts worksheet... I think if we can edit the code to state that Column L (12) on JobCost also needs to match Columns R on the Aging .




Code:
Sub MatchInvNo()
'ActiveWorkbook.RefreshAll

   Dim SrcWS        As Worksheet
   Dim TgtWS        As Worksheet
   Dim TgtRng       As Range
   Dim TgtCel       As Range
   Dim LR           As Long
   Dim myInv        As Range
   Set SrcWS = Sheets("JOBCOST")
   Set TgtWS = Sheets("AGING")

   With TgtWS
      LR = .Range("E" & .Rows.Count).End(xlUp).Row
      Set TgtRng = .Range("E2:E" & LR)
      For Each TgtCel In TgtRng

         Set myInv = SrcWS.Columns(11).Find(TgtCel.Value, , xlValues, xlPart, xlByRows, xlNext, False)
         If Not myInv Is Nothing Then
            .Cells(TgtCel.Row, "V").Value = SrcWS.Cells(myInv.Row, "F").Value
         End If
      Next TgtCel
   End With
End Sub
Attached Files
File Type: zip EXCELFORUMASSIST1.zip (1.06 MB, 11 views)
Reply With Quote
  #2  
Old 12-06-2016, 10:37 AM
cinstanl cinstanl is offline Using If function in VBA to match data from two sheets Windows 7 64bit Using If function in VBA to match data from two sheets Office 2016
Novice
Using If function in VBA to match data from two sheets
 
Join Date: Dec 2016
Posts: 3
cinstanl is on a distinguished road
Default

I need to close this, but do not see an option to do so.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
Using If function in VBA to match data from two sheets Index Match function jackzha Excel 5 12-03-2014 12:43 PM
compare, match and count cell contents between sheets bobsone1 Excel 11 08-07-2014 10:34 PM
MATCH Function namedujour Excel 1 07-20-2012 12:12 AM
If two geographical data match in two sheets, copy unique id/code found in one sheet alliage Excel 1 09-01-2011 05:23 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft