I've scoured the internet trying to find some code that will do what I need, but I have not found anything I can adapt to do this. I think it must be pretty simple, but it is beyond my ability to create it myself.
Please see the attached workbook. I'd really appreciate any help you can give me. Thanks!
Re: VBA to match value in one column to another and paste adjacent value
What about a formula like
G6 = IF(O6=B6,P6,"")
Re: VBA to match value in one column to another and paste adjacent value
Quote from PCI;772134What about a formula like
G6 = IF(O6=B6,P6,"")
Most days I will have multiple entries for some stations. I'm just trying to get the first one in without keying it and follow up by keying any other amounts for that station that day. I can't use a formula (like SUMIF), as the cells need to show what the values are composed of so the users who take it from there can see the amounts of the individual transactions (=2500.25+1612.00+53.50) for the work they have to do after I fill it in.
Re: VBA to match value in one column to another and paste adjacent value
It means you don't need to copy the value from column "O" to "G" but the formula from column "O" with the same references.
Is it what you need ...? So far there is no formula today in column "O"
Re: VBA to match value in one column to another and paste adjacent value
Each day I begin by pasting two columns of data in columns O and B. I'm just trying to get the data in column O over to column G by matching the station call letters. I realize that if one station has two entries in column O on a particular day, that only one of them with go into column G. I will have to later edit the cell in col G to add the amount that did not go when the macro ran.
You gave me this code a couple of months ago that compares stations and amounts to a list of station and amounts, then copies any amounts that match both criteria to specific column/row. What I hoped for this time was to just compare col O to col B and when they match, copy the adjacent value from column P to column G. Perhaps it would just be a case of modifying this code?
Option Explicit
Sub Treat()
Dim ObjDic As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim LR As Long
Dim I As Long
Dim WkDate As Date
Const FR As Integer = 7 ' First Row of data
Dim Temp
Application.ScreenUpdating = False
WkDate = Range("I1")
With ObjDic
LR = Range("M" & Rows.Count).End(3).Row
For I = FR To LR
If (Cells(I, "N") <> "") Then .Item(Cells(I, "M").Value & "/" & Cells(I, "N").Value) = Cells(I, "N").Value
Next
LR = Range("A" & Rows.Count).End(3).Row
For I = FR To LR
If (Cells(I, "B") <> "") Then
Temp = Cells(I, "A").Value & "/" & Cells(I, "B").Value
If (.exists(Temp)) Then
Cells(I, "C") = Format$(WkDate, "mm/dd/yy")
Cells(I, "D") = Cells(I, "B")
.Item(Temp) = ""
End If
End If
Next I
LR = Range("M" & Rows.Count).End(3).Row
For I = FR To LR
If (Cells(I, "N") <> "") Then
Temp = Cells(I, "M").Value & "/" & Cells(I, "N").Value
If (.exists(Temp)) Then Cells(I, "N") = ""
End If
Next
End With
Application.ScreenUpdating = True
End SubRe: VBA to match value in one column to another and paste adjacent value
"If the station in col O matches the station in col B, paste the adjacent value from column P into the corresponding cell in col G."
Do we need to clear the value in column P when it has been added into column G ?
Re: VBA to match value in one column to another and paste adjacent value
Is it what you need ??
Try and comment
Option Explicit
Sub Treat()
Dim ObjDic As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim WkRg As Range
Dim Rg As Range
Dim LR As Long
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
'--- Prepare Dictionary with Station and Credit Cards
Set WkRg = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg
If (Rg <> "") Then
ObjDic.Item(Rg.Value) = Cells(Rg.Row, CCCol2).Value
End If
Next
'--- Prepare Dictionary with Station and Credit Cards
Set WkRg = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
For Each Rg In WkRg
If (Rg <> "") Then
If (ObjDic.exists(Rg.Value)) Then
Cells(Rg.Row, CCCol1) = ObjDic.Item(Rg.Value)
End If
End If
Next
End SubRe: VBA to match value in one column to another and paste adjacent value
Quote from PCI;772358"If the station in col O matches the station in col B, paste the adjacent value from column P into the corresponding cell in col G."
Do we need to clear the value in column P when it has been added into column G ?
I ran the code and it works great. It would be awesome if the value in column P was cleared after it was added to column G, as I could immediately see which amounts were left for me to enter manually. Otherwise, I will need to review the paper version of the document and cross off the ones that placed in order to determine which ones I need to enter manually.
Re: VBA to match value in one column to another and paste adjacent value
Take care today macro does not add the value from column "P" to column "G", it replaces the value and today column "G" is empty:
Do we need to add column "G" with column "P" value ?
Re: VBA to match value in one column to another and paste adjacent value
Here another one
Column "G" value is added with column "P" value
Sub Treat1()
Dim WkRg1 As Range, WkRg2 As Range
Dim Rg As Range
Dim LR As Long
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
Dim Temp
Set WkRg1 = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
Set WkRg2 = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg1
If (Rg <> "") Then
Set Temp = WkRg2.Find(Rg.Value)
If (Not Temp Is Nothing) Then
Cells(Rg.Row, CCCol1) = Cells(Rg.Row, CCCol1) + Cells(Temp.Row, CCCol2)
Cells(Temp.Row, CCCol2) = ""
End If
End If
Next
End SubRe: VBA to match value in one column to another and paste adjacent value
Quote from PCI;772362Take care today macro does not add the value from column "P" to column "G", it replaces the value and today column "G" is empty:
Do we need to add column "G" with column "P" value ?
The code you just gave me is fantastic. Yes, it would be wonderful if there was a way to add the second instance of an amount to the first in column G, but the result would always have to show the individual amounts in the cell with a "+" between them, i.e. "=2500+1540" instead of the total of 4,040. If that's not possible, no problem. I am grateful for what you have given me.
Re: VBA to match value in one column to another and paste adjacent value
See here next code: Check and double check
Sub Treat2()
Dim WkRg1 As Range, WkRg2 As Range
Dim Rg As Range
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
Dim F
Dim T, TT
Set WkRg1 = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
Set WkRg2 = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg1
If (Rg <> "") Then
Set F = WkRg2.Find(Rg.Value)
If ((Not F Is Nothing) And (Cells(F.Row, CCCol2) <> "")) Then
T = Cells(Rg.Row, CCCol1).Formula
T = IIf(InStr(T, "=") = 0, "=" & T, T)
TT = T & "+" & Cells(F.Row, CCCol2)
Cells(Rg.Row, CCCol1).Formula = TT
Cells(F.Row, CCCol2) = ""
End If
End If
Next
End SubRe: VBA to match value in one column to another and paste adjacent value
Here a remake to avoid any possible issue with the Station
Sub Treat3()
Dim WkRg1 As Range, WkRg2 As Range
Dim Rg As Range
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
Dim F
Dim T, TT
Set WkRg1 = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
Set WkRg2 = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg1
If (Rg <> "") Then
Set F = WkRg2.Find(What:=Rg.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If ((Not F Is Nothing) And (Cells(F.Row, CCCol2) <> "")) Then
T = Cells(Rg.Row, CCCol1).Formula
T = IIf(InStr(T, "=") = 0, "=" & T, T)
TT = T & "+" & Cells(F.Row, CCCol2)
Cells(Rg.Row, CCCol1).Formula = TT
Cells(F.Row, CCCol2) = ""
End If
End If
Next
End SubRe: VBA to match value in one column to another and paste adjacent value
Runtime error '91'
Object Variable or With block variable not set
It gave me this error when it got to WYMT in column B. There is no deposit for WYMT this particular day, so is not listed in column O. I wonder if that is an issue?
When I pressed Debug this is the line that was highlighted:
Re: VBA to match value in one column to another and paste adjacent value
See next code
Sub Treat4()
Dim WkRg1 As Range, WkRg2 As Range
Dim Rg As Range
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
Dim F
Dim T, TT
Set WkRg1 = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
Set WkRg2 = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg1
If (Rg <> "") Then
Set F = WkRg2.Find(What:=Rg.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If (Not F Is Nothing) Then
If (Cells(F.Row, CCCol2) <> "") Then
T = Cells(Rg.Row, CCCol1).Formula
T = IIf(InStr(T, "=") = 0, "=" & T, T)
TT = T & "+" & Cells(F.Row, CCCol2)
Cells(Rg.Row, CCCol1).Formula = TT
Cells(F.Row, CCCol2) = ""
End If
End If
End If
Next
End SubRe: VBA to match value in one column to another and paste adjacent value
This code ran without error, but did not add the additional values to the cells.
Re: VBA to match value in one column to another and paste adjacent value
Can you send a sample of the file with the issue.
If there is no value in column"O" there is nothing to add ...no?
Re: VBA to match value in one column to another and paste adjacent value
forum.ozgrid.com/index.php?attachment/69384/
I've attached a copy of the original file with the results of having run the most recent code. I've highlighted the stations in column O and P to show where there were two instances of station amounts but only the first one pasted to column G.
Re: VBA to match value in one column to another and paste adjacent value
Clear now,
Yes it was not supposed to have several times the same Station in column "G".
There is a solution on preparation
Re: VBA to match value in one column to another and paste adjacent value
Here we are:check, check, check again ...!
Sub Treat5()
Dim WkRg1 As Range, WkRg2 As Range
Dim Rg As Range
Const FR As Integer = 6 ' First Row
Const StatCol1 As String = "B"
Const StatCol2 As String = "O"
Const CCCol1 As String = "G"
Const CCCol2 As String = "P"
Dim F
Dim T, TT
Dim FIRST_Add
Set WkRg1 = Range(Cells(FR, StatCol1), Cells(Rows.Count, StatCol1).End(3))
Set WkRg2 = Range(Cells(FR, StatCol2), Cells(Rows.Count, StatCol2).End(3))
For Each Rg In WkRg1
If (Rg <> "") Then
Set F = WkRg2.Find(What:=Rg.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
FIRST_Add = F.Address
Do
If (Cells(F.Row, CCCol2) <> "") Then
T = Cells(Rg.Row, CCCol1).Formula
T = IIf(InStr(T, "=") = 0, "=" & T, T)
TT = T & "+" & Cells(F.Row, CCCol2)
Cells(Rg.Row, CCCol1).Formula = TT
Cells(F.Row, CCCol2) = ""
End If
Set F = WkRg2.FindNext(F)
Loop While (F.Address <> FIRST_Add)
End If
Next
End SubDon’t have an account yet? Register yourself now and be a part of our community!