Simple macro to change values in second sheet

F

financialdoc

New Member
Joined
Jan 27, 2005
Messages
10
I need help with this macro. I'm new to the VBA thing and any help is greatly appreciated.

I have two sheets that need to be synchronized. The ranges have different amounts of cells, and so I'm not sure if I need to set up an array or not. Basically if the values column "A" and "B" in sheet 1 match up with column "A" and "B" in sheet two then I want the value in Column "C" of sheet 2 to be "TRUE" (for that row)

Here is what I have so far (and my Do statement is wrong already):

Sub Synchronize()

Dim arng As Range
Dim arng2 As Range
Dim arng3 As Range
Dim brng As Range
Dim brng2 As Range
Dim brng3 As Range

Set arng = Range("Sheet1!C2")
Set arng2 = Range("Sheet1!D2")
Set arng3 = Range("Sheet1!F2")
Set brng = Range("Sheet2!b2")
Set brng2 = Range("Sheet2!c2")
Set brng3 = Range("Sheet2!ax2")

arang3.Activate

Do

If arng3 = "Open" Then
If arng = brng And arng2 = brng2 Then
brng3.Value = "True"
End If
If arng3 = "Closed" Then
If arng = brng And arng2 = brng2 Then
brng3.Value = "True"
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell)


End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I'm a little further along (and fixed the do loop). Here is what I have so far...

Sub Synchronize()

Dim arng As Range
Dim arng2 As Range
Dim arng3 As Range
Dim brng As Range
Dim brng2 As Range
Dim brng3 As Range
Dim i As Integer

Set arng = Range("Sheet1!C:C")
Set arng2 = Range("Sheet1!D:D")
Set arng3 = Range("Sheet1!F:F")
Set brng = Range("Sheet2!B:B")
Set brng2 = Range("Sheet2!C:C")
Set brng3 = Range("Sheet2!AX:AX")



Worksheets("Sheet1").Activate
Range("F:F").Select
For i = 1 To Selection.CurrentRegion.Rows.Count - 1
If arng3 = "Open" Then
If arng = brng And arng2 = brng2 Then
Worksheets("Sheet2").Activate
brng3.Value = "True"
End If
End If
If arng3 = "Closed" Then
If arng = brng And arng2 = brng2 Then
Worksheets("Sheet2").Activate
brng3.Value = "True"
End If
Worksheets("Sheet1").Activate
End If
Next i

End Sub
 
Upvote 0
I'm a little further along, but I still need help :oops:

My issues are getting the macro to search down the column, then find a match, record the row number, go to the other two columns in the row, record the values, and then match up those values with two columns in the second sheet, and if a positive match is found, to change the value in the third column of the second sheet. Whew!

PLEASE HELP!! :rolleyes:

Here's what I have so far:


Sub SynchronizeMe()
Dim I As Integer
Set arng = ActiveWorkbook.Worksheets("Sheet1").Columns("F")
Set brng = ActiveWorkbook.Worksheets("Sheet1").Columns("C")
Set crng = ActiveWorkbook.Worksheets("Sheet1").Columns("D")
Set drng = ActiveWorkbook.Worksheets("Sheet2").Columns("B")
Set erng = ActiveWorkbook.Worksheets("Sheet2").Columns("C")
Set frng = ActiveWorkbook.Worksheets("Sheet2").Columns("AX")


For I = 1 To arng.CurrentRegion.Rows.Count - 1
If arng.Cells.Value = "Open" Then
Set OpenRow = arng.Rows
Set State = Range(OpenRow, brng).Value
Set County = Range(OpenRow, crng).Value
Set currentrow = frng.Rows
If State = Range(currentrow, drng).Value And County = Range(currentrow, erng).Value Then
Range(currentrow, frng).Value = "True"
End If
ElseIf arng.Cells.Value = "Closed" Then
Set OpenRow = arng.Rows
Set State = Range(OpenRow, brng).Value
Set County = Range(OpenRow, crng).Value
Set currentrow = frng.Rows
If State = Range(currentrow, drng).Value And County = Range(currentrow, erng).Value Then
Range(currentrow, frng).Value = "True"
End If
End If
ActiveCell.Offset(1, 0).Activate
Next I
End Sub
 
Upvote 0
I reworked your code. It should be doing the same thing you were trying above, I just simplified it a little (you don't need to set a variable for every column ;) )

As your code is written now, it is doing the exact same thing if it finds "Open" or "Closed." Somehow, I don't think that's really what you want--though I could be wrong. As it stands right now I just went with what you had and left that as is, but this seems to be working for me.

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range, rngF <SPAN style="color:#00007F">As</SPAN> Range, rngAZ <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> CurrentRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, State <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, County <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Set</SPAN> rngF = Sheets("Sheet1").Range("F1", Sheets("Sheet1").Range("F65536").End(xlUp))

rngF.Select
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rngF
    <SPAN style="color:#00007F">If</SPAN> cell.Value = "Open" <SPAN style="color:#00007F">Or</SPAN> "Closed" <SPAN style="color:#00007F">Then</SPAN>
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet2").Cells(CurrentRow, "B").Value = State And _
            Sheets("Sheet2").Cells(CurrentRow, "C").Value = County <SPAN style="color:#00007F">Then</SPAN>
            Sheets("Sheet1").Cells(CurrentRow, "AZ").Value = "TRUE"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> cell
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Thanks so much for helping me! you're my hero!! :pray:

You're right, I didn't mean to have "Open" and "Close" yield the same result. I have a couple of complications though...

Here is my "Sheet2" (Sorry for the &nbsp spaces)
vba test 3.xls
ABCDE
1STCountyCourtProductionViewable
21ALAutaugaCircuitCourtofAutaugaCountyTRUE
32ALBaldwinCircuitCourtofBaldwinCounty
43ALBarbourCircuitCourtofBarbourCounty
54ALBarbourBarbourCountyCircuitCourt
65ALBibbCircuitCourtofBibbCounty
76ALBlountCircuitCourtofBlountCounty
87ALBullockCircuitCourtofBullockCounty
98ALButlerCiruitCourtofButlerCounty
Sheet2


Below is my "Sheet1"
vba test 3.xls
ABCDEF
1IDStateCountyPhaseStatus
2105ProductionHoldALAutaugaInitialPleadingsOpen
3106ProductionHoldALAutaugaDefaultJudgmentsClosed
4107ProductionHoldALAutaugaConsentJudgmentsClosed
5108ProductionHoldALAutaugaGarnishmentsClosed
6109ProductionHoldALBaldwinInitialPleadingsOpen
7110ProductionHoldALBaldwinDefaultJudgmentsClosed
8111ProductionHoldALBaldwinConsentJudgmentsClosed
9112ProductionHoldALBaldwinGarnishmentsClosed
10113ProductionHoldALBarbourInitialPleadingsOpen
11114ProductionHoldALBarbourDefaultJudgmentsClosed
12115ProductionHoldALBarbourConsentJudgmentsClosed
13116ProductionHoldALBarbourGarnishmentsClosed
14117ProductionHoldALBibbInitialPleadingsClosed
15118ProductionHoldALBibbDefaultJudgmentsClosed
16119ProductionHoldALBibbConsentJudgmentsClosed
17120ProductionHoldALBibbGarnishmentsClosed
18121ProductionHoldALBlountInitialPleadingsOpen
Sheet1


As you can see there are instances where the values of "State" and "CountY" would be repeated, and I think it is getting hung up when that happens. You can see the result of the macro in "Sheet2"

Here is where I am at right now:

Code: 
Sub test()
Dim cell As Range, rngF As Range, rngAZ As Range
Dim CurrentRow As Long, State As String, County As String

Set rngF = Sheets("Sheet1").Range("F2", Sheets("Sheet1").Range("F65536").End(xlUp))
Set rngAZ = Sheets("Sheet2").Range("B2", Sheets("Sheet2").Range("B65536").End(xlUp))

Worksheets("Sheet1").Activate
rngF.Select
For Each cell In rngF
    If cell.Value = "Open" And _
    cell.Offset(0, -1).Value = "Initial Pleadings" Then
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        If Sheets("Sheet2").Cells(CurrentRow, "B").Value = State And _
            Sheets("Sheet2").Cells(CurrentRow, "C").Value = County Then
            Sheets("Sheet2").Cells(CurrentRow, "E").Value = "TRUE"
        End If
    If cell.Value = "Closed" And _
    cell.Offset(0, -1).Value = "Initial Pleadings" Then
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        If Sheets("Sheet2").Cells(CurrentRow, "B").Value = State And _
            Sheets("Sheet2").Cells(CurrentRow, "C").Value = County Then
            Sheets("Sheet2").Cells(CurrentRow, "E").Value = "FALSE"
        End If
      End If
      End If
      
Next cell
        
End Sub
 
Upvote 0
There are a couple of things I see going on with the code here:

1) Not every instance of "Open" and "Initial Pleadings" is being marked as True because the State and County on Sheet1 are not matching the State and County of the same row on Sheet2.

You may have to give a bit more information as to how you want to match the variables. It is currently grabbing the State/County from Sheet1, making a note of that row number, and checking the same row number on Sheet2 to see if the State/County matches. Is that what it should be doing?

2) It currently isn't checking for "Closed" at all. You just need to rewrite the If statement a little.

The following should fix so it checks for both Open and Closed:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range, rngF <SPAN style="color:#00007F">As</SPAN> Range, CurrentRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> State <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, County <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Set</SPAN> rngF = Sheets("Sheet1").Range("F2", Sheets("Sheet1").Range("F65536").End(xlUp))

Worksheets("Sheet1").Activate

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rngF
    <SPAN style="color:#00007F">If</SPAN> cell.Value = "Open" And _
    cell.Offset(0, -1).Value = "Initial Pleadings" <SPAN style="color:#00007F">Then</SPAN>
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet2").Cells(CurrentRow, "B").Value = State And _
            Sheets("Sheet2").Cells(CurrentRow, "C").Value = County <SPAN style="color:#00007F">Then</SPAN>
            Sheets("Sheet2").Cells(CurrentRow, "E").Value = "TRUE"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">If</SPAN> cell.Value = "Closed" And _
            cell.Offset(0, -1).Value = "Initial Pleadings" <SPAN style="color:#00007F">Then</SPAN>
            CurrentRow = cell.Row
            State = cell.Offset(0, -3).Value
            County = cell.Offset(0, -2).Value
            <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet2").Cells(CurrentRow, "B").Value = State And _
                Sheets("Sheet2").Cells(CurrentRow, "C").Value = County <SPAN style="color:#00007F">Then</SPAN>
                Sheets("Sheet2").Cells(CurrentRow, "E").Value = "FALSE"
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> cell
      
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Kristy,

YOU ARE MY HERO!!!! :pray: :pray: :pray:

Thanks so much for your help. You identified the problems that I had. I took what you gave me and worked it so that it could find my instances where the counties and states matched on both sheets and then put "TRUE" for the counties that were "Open" and "False" for the counties that are "Closed".

Here's the code I finally ended up with....

Code: 
Sub test2()
Dim cell As Range, rngF As Range, rngB As Range, CurrentRow As Long, CurrentRow2 As Long

Dim State As String, County As String

Set rngF = Sheets("Sheet1").Range("F2", Sheets("Sheet1").Range("F65536").End(xlUp))
Set rngB = Sheets("Sheet2").Range("B2", Sheets("Sheet2").Range("B65536").End(xlUp))

For Each cell In rngF
    If cell.Value = "Open" And _
    cell.Offset(0, -1).Value = "Initial Pleadings" Then
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        For Each cell2 In rngB
        If cell2.Value = State And _
        cell2.Offset(0, 1).Value = County Then
               CurrentRow2 = cell2.Row
               Sheets("Sheet2").Cells(CurrentRow2, "E").Value = "TRUE"
        End If
        Next cell2
    Else
        If cell.Value = "Closed" And _
    cell.Offset(0, -1).Value = "Initial Pleadings" Then
        CurrentRow = cell.Row
        State = cell.Offset(0, -3).Value
        County = cell.Offset(0, -2).Value
        For Each cell2 In rngB
        If cell2.Value = State And _
        cell2.Offset(0, 1).Value = County Then
              CurrentRow2 = cell2.Row
              Sheets("Sheet2").Cells(CurrentRow2, "E").Value = "FALSE"
        End If
        Next cell2
        End If
    End If
    
    
Next cell
       
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Sort Cell Columns VBA
Replies
7
Views
666
ststern45
S
T
  • Question
VBA compare values on different sheets.
Replies
5
Views
77
rlv01
R
K
  • Question
how to copy above cell values down
Replies
2
Views
83
Cubist
Cubist
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
264
birdman15
B
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
53
brianv
B

Forum statistics

Threads
1,215,517
Messages
6,125,287
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top