I am using VBA and Oracle Objects for OLE to query an Oracle database
and pull values into a spreadsheet. I have to test the values of cells
in two columns of data and based on the contents of these values
insert a value into a third column.

For example:

If the value in Cell F2 = 'A' AND the value in Cell G2 = 1 THEN insert
the value 'V1' into Cell H2

If the value in Cell F2 = 'A' AND the value in Cell G2 = 2 THEN insert
the value 'V3' into Cell H2

etc.

Here are the actual combinations:

Value1 Value2 Value to Insert

New Trans Abandoned Customer opt out
Regular Abandoned in queue Abandoned
Regular Abandoned while ringing agent Abandoned
Conf/Trans All other cases Transfer to CCT
Regular All other cases Handled by CSR
Transfer All other cases Transfer to CCT
Conference Call disconnected by agent Handled by CSR
Regular Call disconnected by agent Handled by CSR
Emergency Call disconnected by agent Handled by CSR
Conference Call disconnected by caller Handled by CSR
Regular Call disconnected by caller Handled by CSR
Conf/Trans Terminated by transfer Transfer to extension

Can someone please help me with the code to loop through the two
columns and test the values and populate the third column. Everything
I try keeps blowing up. Right now I am running into a dead end with
populating a two dimensional array. I think this method is not the
easiest. Help!

Here is some code for you. I was not sure if by v1 you meant value 1 or the
value in cell V1. I went with the value in V1.

Public Sub Poplulate()
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rng = wks.Range("F65536").End(xlUp)

Do While rng.Row 1
Select Case rng.Value
Case "A"
Select Case rng.Offset(0, 1).Value
Case 1
rng.Offset(0, 2).Value = wks.Range("V1").Value
Case 2
rng.Offset(0, 2).Value = wks.Range("V2").Value
Case Else
rng.Offset(0, 2).Value = wks.Range("V5").Value
End Select
Case "B"
Select Case rng.Offset(0, 1).Value
Case 1
rng.Offset(0, 2).Value = wks.Range("V3").Value
Case 2
rng.Offset(0, 2).Value = wks.Range("V4").Value
Case Else
rng.Offset(0, 2).Value = wks.Range("V5").Value
End Select
Case Else
rng.Offset(0, 2).Value = wks.Range("V5").Value
End Select
Set rng = rng.Offset(-1, 0)
Loop
End Sub

--
HTH...

Jim Thomlinson


" wrote:



I am using VBA and Oracle Objects for OLE to query an Oracle database
and pull values into a spreadsheet. I have to test the values of cells
in two columns of data and based on the contents of these values
insert a value into a third column.

For example:

If the value in Cell F2 = 'A' AND the value in Cell G2 = 1 THEN insert
the value 'V1' into Cell H2

If the value in Cell F2 = 'A' AND the value in Cell G2 = 2 THEN insert
the value 'V3' into Cell H2

etc.

Here are the actual combinations:

Value1 Value2 Value to Insert

New Trans Abandoned Customer opt out
Regular Abandoned in queue Abandoned
Regular Abandoned while ringing agent Abandoned
Conf/Trans All other cases Transfer to CCT
Regular All other cases Handled by CSR
Transfer All other cases Transfer to CCT
Conference Call disconnected by agent Handled by CSR
Regular Call disconnected by agent Handled by CSR
Emergency Call disconnected by agent Handled by CSR
Conference Call disconnected by caller Handled by CSR
Regular Call disconnected by caller Handled by CSR
Conf/Trans Terminated by transfer Transfer to extension

Can someone please help me with the code to loop through the two
columns and test the values and populate the third column. Everything
I try keeps blowing up. Right now I am running into a dead end with
populating a two dimensional array. I think this method is not the
easiest. Help!