Changing cell values

[terp26]

Ok heres the situation. I have 2 spreadsheets open with an amount column in one and the same in the other. I need to run a program to match the amount in one column to the other. As well as making sure the ID is LIKE the ID in the otehr spreadsheet (in case of typos etc) If they do match, I would need to change another cell to a value. I have an idea of what kind of code to write, I just don't know how to get started using VBA

[Garratt]

If I had a clearer picture of what you are trying to do, I could help you out here.

You have two workbooks, each with an "amount" column..
Each sheet also has an ID column...

You want to (say) scan one sheet and if the ID column matches, copy the amount to the other sheet as well as do something else to another cell in an (unknown) location?

Do the ID's on one sheet row by row match each other?


I need a screenshot or something.

[terp26]

Ok well 2 spreadsheets. I want to match one amount column with another amount column in another spreadsheet. If there is a match I want a cell in that row to change and say "July-03" for example.

The ID for that amount should also match with each other, and there should be the same ID in both spreadsheets however in case of errors I wouldnt want it to search for an exact ID value. Let me know if that helps.

[Garratt]

(tip) It's easier if you talk like this:
A spreadsheet file is a WORKBOOK
A sheet in a workbook is called a WORKSHEET

Assuming you have two seperat workbooks and both of them have the same number of rows with the data in the same order. And assuming the ID column is column 1 and the Amount Column is Column 2, you could use this:

 Public Sub test()
Dim WB1_LastRow As Integer
Dim WB2_LastRow As Integer
Dim TempStringA As String
Dim TempStringB As String

Set WBK1 = Workbooks("My Workbook1.xls")
Set WBK2 = Workbooks("My Workbook2.xls")
Set WBK1S1 = WBK1.Worksheets(1)
Set WBK2S1 = WBK2.Worksheets(1)

WBK1S1.Activate

LastRowWB1 = Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row

For X = 2 To LastRowWB1
    WBK1S1.Activate
    TempStringA = Cells(X, 1).Value
    TempStringB = Cells(X, 2).Value
    WBK2S1.Activate
    If TempStringA = Cells(X, 1).Value And TempStringB = Cells(X, 2).Value Then
        Cells(X, 2).Value = "JUL-04"
    End If
Next X

End Sub

If the columns in the two Workbooks are different lenghts and the data is not in order, you can use this:

Public Sub test()
Dim X As Integer
Dim Y As Integer
Dim LastRowWB1 As Integer
Dim LastRowWB2 As Integer
Dim TempStringA() As String
Dim TempStringB() As String
Dim ArrayLength As Integer


Set WBK1 = Workbooks("My Workbook1.xls")
Set WBK2 = Workbooks("My Workbook2.xls")
Set WBK1S1 = WBK1.Worksheets(1)
Set WBK2S1 = WBK2.Worksheets(1)

WBK1S1.Activate
LastRowWB1 = Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
ArrayLength = LastRowWB1 - 1
ReDim TempStringA(ArrayLength)
ReDim TempStringB(ArrayLength)

For X = 2 To LastRowWB1
    TempStringA(X - 1) = Cells(X, 1).Value
    TempStringB(X - 1) = Cells(X, 2).Value
Next X

WBK2S1.Activate
LastRowWB2 = Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row

For X = 2 To LastRowWB2
    For Y = 1 To ArrayLength
        If TempStringA(Y) = Cells(X, 1).Value And TempStringB(Y) = Cells(X, 2).Value Then
            Cells(X, 2).Value = "JUL-04"
            Exit For
        End If
    Next Y
Next X

End Sub

If you don't understand what something is doing I'll explain.
If you put your curors in the middle of a function or command and press "F1" - you will get the help file on it.

Good Luck


If the data is not in order