problem with comparing strings in VBA

  • Thread starter Thread starter catchchatur
  • Start date Start date
C

catchchatur

Experts,

I need to copy data from once excel file to other based on string
comparison. For example consider two workboooks.

1. Source file

These are the contets in column A of source file corresponding

A1 - "Year"
A2 - "Month"
A3 - "Date"
A4 - "Hour"

2. target file

These are the contets column B in target file corresponding

B1 - "Year"
B2 - "Month"
B3 - "Date"
B4 - "Hour"

So if contents of cell A1 = contents of cell B1
AND
contents of cell A2 = contents of cell B2
AND
contents of cell A3 = Contents of cell B3
AND
contents of cell A4 = Contents of cell B4

Then
Copy contents C1:C4 from source to C1:C4 in target.

I am trying to do it with following code, but the problem is that my
code is copying the contents of range in cells columns "C" even when
contents of colums in A in source does not match with contents of
column B in target. Can somebody help??

This is what I have so far:

Dim source_column As Long
Dim source_row1 As Long
Dim source_row2 As Long
Dim source_row3 As Long
Dim source_row4 As Long

Dim target_column As Long
Dim target_row1 As Long
Dim target_row2 As Long
Dim target_row3 As Long
Dim target_row4 As Long

Dim source_string1 As String
Dim source_string2 As String
Dim source_string3 As String
Dim source_string4 As String

Dim target_string1 As String
Dim target_string2 As String
Dim target_string3 As String
Dim target_string4 As String

source_row1 = 1
source_row2 = 2
source_row3 = 3
source_row4 = 4
target_row1 = 1
target_row2 = 2
target_row3 = 3
target_row4 = 4

For target_column = 4 To 27
For source_column = 3 To 17
target_string1 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row1,
target_column).Value
source_string1 = wbSource.Worksheets("sheet1").Cells(source_row1,
source_column).Value
If target_string1 = source_string1 Then
target_string2 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row2,
target_column).Value
source_string2 =
wbSource.Worksheets("sheet1").Cells(source_row2, source_column).Value
If target_spring2 = target_spring2 Then
target_string3 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row3,
target_column).Value
source_string3 =
wbSource.Worksheets("sheet1").Cells(source_row3, source_column).Value
If target_spring3 = target_spring3 Then
target_string4 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row4,
target_column).Value
source_string4 =
wbSource.Worksheets("sheet1").Cells(source_row4, source_column).Value
If target_spring4 = target_spring4 Then
MsgBox "Strings matched are " & target_string1 &
source_string1 & target_string2 & source_string2 & target_string3 &
source_string3 & target_string4 & source_string4
:::::
::::::::
End If
End If
End If
End If
Next
Next
 
Experts,

I need to copy data from once excel file to other based on string
comparison. For example consider two workboooks.

1. Source file

These are the contets in column A of source file corresponding

A1 - "Year"
A2 - "Month"
A3 - "Date"
A4 - "Hour"

2. target file

These are the contets column B in target file corresponding

B1 - "Year"
B2 - "Month"
B3 - "Date"
B4 - "Hour"

So if contents of cell A1 = contents of cell B1
AND
contents of cell A2 = contents of cell B2
AND
contents of cell A3 = Contents of cell B3
AND
contents of cell A4 = Contents of cell B4

Then
Copy contents C1:C4 from source to C1:C4 in target.

I am trying to do it with following code, but the problem is that my
code is copying the contents of range in cells columns "C" even when
contents of colums in A in source does not match with contents of
column B in target. Can somebody help??

This is what I have so far:

Dim source_column As Long
Dim source_row1 As Long
Dim source_row2 As Long
Dim source_row3 As Long
Dim source_row4 As Long

Dim target_column As Long
Dim target_row1 As Long
Dim target_row2 As Long
Dim target_row3 As Long
Dim target_row4 As Long

Dim source_string1 As String
Dim source_string2 As String
Dim source_string3 As String
Dim source_string4 As String

Dim target_string1 As String
Dim target_string2 As String
Dim target_string3 As String
Dim target_string4 As String

source_row1 = 1
source_row2 = 2
source_row3 = 3
source_row4 = 4
target_row1 = 1
target_row2 = 2
target_row3 = 3
target_row4 = 4

For target_column = 4 To 27
For source_column = 3 To 17
target_string1 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row1,
target_column).Value
source_string1 = wbSource.Worksheets("sheet1").Cells(source_row1,
source_column).Value
If target_string1 = source_string1 Then
target_string2 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row2,
target_column).Value
source_string2 =
wbSource.Worksheets("sheet1").Cells(source_row2, source_column).Value
If target_spring2 = target_spring2 Then
target_string3 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row3,
target_column).Value
source_string3 =
wbSource.Worksheets("sheet1").Cells(source_row3, source_column).Value
If target_spring3 = target_spring3 Then
target_string4 =
wbTarget.Worksheets("bilateralgaspurchase").Cells(target_row4,
target_column).Value
source_string4 =
wbSource.Worksheets("sheet1").Cells(source_row4, source_column).Value
If target_spring4 = target_spring4 Then
MsgBox "Strings matched are " & target_string1 &
source_string1 & target_string2 & source_string2 & target_string3 &
source_string3 & target_string4 & source_string4
:::::
::::::::
End If
End If
End If
End If
Next
Next
Click to expand...

This seems a hard way to do it. But the problem that you are having
is that your if statments are comparing the same variable and it is
not the variable you think it is. You are comparing TARGET_SPRING4 to
TARGET_SPRING4 when I think you want TARGET_STRING4=SOURCE_STRING4
The only one that is correct is the 1st one.

Pete
 
Thanks!!

I corrected it meanwhile.. :)

Thanks

This seems a hard way to do it. But the problem that you are having
is that your if statments are comparing the same variable and it is
not the variable you think it is. You are comparing TARGET_SPRING4 to
TARGET_SPRING4 when I think you want TARGET_STRING4=SOURCE_STRING4
The only one that is correct is the 1st one.

Pete- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top