VBA Excel: Don't Kknow right syntax to Set a variable's value 1

[RSJohnson]

I am matching data in cells on different worksheets in the same workbook. When the data matches I copy some data from sheet 1 to sheet 2.

If they do not match I insert a string where the data would have gone on sheet 2. In many cases there will be multiple records with the same unmatchable data element, I need cycle through all such records until I get to a different data element. The string is inserted during each iteration where the data would have gone on sheet 2.

When a new data element is reached and if that element turns out to be another element that has no match in sheet 1, I get a; Runtime error 91; ‘Object variable or with block variable not set’. The variable rngGMPCA has no value because a match to variable DAFRPCA could not be found. I have tried to assign a value to GMPCA but I am obviously not using the correct syntax. Can someone tell me how to assign a value to GMPCA at the ‘Set GMPCA …. ‘ line in the code below.



With Sheets("First QT").Range("B1:B2034")
Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
'Moves to next GM PCA and test for match.
Set rngToCopy = rngToCopy.Offset(1, 0)

If rngGMPCA Is Nothing Then
'Records comment in cell with DAFR PCA to alert user
to non-matching problem all the DAFRPCAs have been tested
rngDestin = "No data retreived."
strEscape = rngDAFRPCA.Value

Do Until strEscape <> rngDAFRPCA.Value

'Moves to next GM PCA and test for match.
Set rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)
rngDestin = "No data retreived."
Loop

With Sheets("First QT").Range("B1:B2034")
Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)

If rngGMPCA Is Nothing Then
'Set rngGMPCA =
End If

Set rngToCopy = rngToCopy.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)

End With
End If
End With
End If
Loop
End Sub

 

[SkipVought]

semaphore,

1. The Find method requires a variant and not a range object as an argument.

2. the bulk of your code (incomplete) is where there is NO FIND OBJECT???

On the sheet containing the values you are searching for, are the search values in ascending order? I prefer to sort the target table in ascending order, use the MATCH function to find the FIRST occurrence (or not) and then having found a MATCH, test the NEXT row's value. Much cleaner.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

1. The Find method requires a variant and not a range object as an argument.

The code works for the first 325 records. There are 16 instances where it must fine a match and it appears to do so. Only when there are two consecutive non-matches does it invoke a runtime error and stop. Is this because I think I am declaring a variable that is a range and by default a variant is actually being declared?

2. the bulk of your code (incomplete) is where there is NO FIND OBJECT???

Could you further explain this?

3. On the sheet containing the values you are searching for, are the search values in ascending order? I prefer to sort the target table in ascending order, use the MATCH function to find the FIRST occurrence (or not) and then having found a MATCH, test the NEXT row's value. Much cleaner.

On both sheets the elements that are being matched are sorted in ascending order. I considered “Match” but could not figure what I would do with position of the match. So not knowing “1” above I opted for Find.

 

[SkipVought]

1) use FIND to get the FIRST occurrence

2) use a row variable to loop thru the rows until you have no match

Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
If Not rngGMPCA is Nothing then
  lRow = rngGMPCA.Row
  Do While (Cells(lRow, iCol).value =  rngDAFRPCA.Value)
    'perform whatever
    lRow =lRow +1
  Loop
End if

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

I incorporated the code you suggested after the "Else" and commented out my original code. When I run the code now there is no transfer of data when the DAFRPCA changes. If the code was matching on A100 and the DAFRPCA is now A101, the last line where the DAFRPCA is A100 has no data transferred to it from sheet First QT. Also if the code is in the no match loop the code is not putting “No data Retrieved.” in rngDestin. The transfer of data gets out of synchronization. Code stills stops but at row 358 instead of row 353.

Below is the complete Sub, the bold cod is what I inserted. Is it where you intended?

Sub MatchPCA()
'
' MatchPCA Macro
' Macro recorded 2/20/2004 by Robert S. Johnson

Dim intCounter As Integer, intStop As Integer
Dim rngGMPCA As Range, rngDAFRPCA As Range, rngDestin As Range, rngToCopy As Range
Dim strEscape As String
Dim int1Row As Integer

'This initializes the variable for the first match.
Set rngDAFRPCA = Sheets("Prepared_Expense").Range("Q2").Offset(1, -15) 'Range _
must be in the same row as the start of DAFRPCA data.
Set rngGMPCA = Sheets("First QT").Range("B4") 'Change to variable!
Set rngToCopy = Sheets("First QT").Range("CB4:CE4") 'Change to variable!
Set rngDestin = Sheets("Prepared_Expense").Range("R2") 'Range must be in same row as _
the start of DAFRPCA data.

'Initializes counter.
intCounter = 1

'Get starting cell for GM expenses data.
Do Until intCounter = 2
pstrDAFRStart = InputBox("Enter cell location of the start of DAFR expense data.")
If pstrDAFRStart <> "" Then
Exit Do
Else
If pstrDAFRStart = "" Then
MsgBox "You didn't enter cell location, please do so. "
End If
End If
intCounter = intCounter + 1
If intCounter = 2 Then
Exit Sub
End If

Loop

intStop = Sheets("Prepared_Expense").Range(pstrDAFRStart).CurrentRegion.Rows.Count 'Change to find l

intCounter = 1

Do Until intCounter = intStop
If rngDAFRPCA = rngGMPCA Then
Sheets("First QT").Select
rngToCopy.Select
Selection.Copy
Sheets("Prepared_Expense").Select
rngDestin.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Moves to next row in the expense worksheet and the next destination cell.
Set rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)

'Increments counter.
intCounter = intCounter + 1

Else
With Sheets("First QT").Range("B1:B2034")
Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
If Not rngGMPCA Is Nothing Then
int1Row = rngGMPCA.Row
Do While (Cells(int1Row, 2).Value = rngDAFRPCA.Value)
'perform whatever
rngDestin = "No data retrieved."
int1Row = int1Row + 1
Loop
' Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
'Moves to next GM PCA and test for match.
' Set rngToCopy = rngToCopy.Offset(1, 0)

' If rngGMPCA Is Nothing Then
'Records comment in cell with DAFR PCA to alert user to non-matching problem _
all the DAFRPCAs have been tested.
' rngDestin = "No data retreived."

'Assigns the cueernt value of the DAFRPCA variable to the variable Escape.
' strEscape = rngDAFRPCA.Value

'Loops through all the records where rngDAFRPCA remains the same.
' Do Until strEscape <> rngDAFRPCA.Value

'Moves to next GM PCA and test for match.
' Set rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
' Set rngDestin = rngDestin.Offset(1, 0)
' rngDestin = "No data retreived."
' Loop

'
With Sheets("First QT").Range("B1:B2034")
Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)

If rngGMPCA Is Nothing Then
'Set rngGMPCA =
End If

Set rngToCopy = rngToCopy.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)

End With
End If
End With
End If
Loop

 

[SkipVought]

sem,

please explain what you want your code to do. I can't wade thru what you have.

Describe your data. Describe the process.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Process:
There are two different systems (Grants Management (GM) and Budget Execution (BE)) that are alleged to track the same expenditures but each uses a different characterization of expenditures. The GM expenditures are presented by associated with individual grants. The BE expenditures is presented associated with individual cost center codes called Agency Objects. The common element between the two expenditure reports is a Project Control Account (PCA) (though in on system the PCA is 5 digits and the other it is 4.) The BE expenditures have to be distributed by source of funds (General, Special, Federal and Reimbursable).

To do this I must first categorize the GM expenses by source of funds. Then I must compute the portion of total expenses represented by each category. This gives me a percentage (called a Cost Allocation Method Percent) for each PCA by category. There can be only one CAMP for each PCA but a CAMP can be applied to many different PCAs.

The plan is to summarize GM data by fund source, then compute and store the CAMP. This data is stored on a worksheet named First QT. Then I match PCAs and copy the CAMP to a worksheet named Prepared_Expense which contains the BE expenses and then compute and store the expense distribution

This is what I want my code to do.

1. If variables DAFRPCA and GMPCA match then what is in range (rngStart) of the row of the match from First QT is copied to the range (rngDestin) of the row of the match on Prepared Expense.

2. Keep doing this until the variables don’t match.

3. Then find a GMPCA variable value that matches the DAFRPCA and then do 2.

4. If there is no GMPCA match then for every instance of the current value of DAFRPCA enter “No data retrieved” in rngDestin.

5. When there is a new value for DAFRPCA then find its match among the values for GMPCA and do 2.

6. If there is no GMPCA match then for every instance of the current value of DAFRPCA enter “No data retrieved” in rngDestin

7. Assign a value to GMPCA and move to the next row in both sheets (This was attempting my to over come the instance where there are at least two consecutive no matches and GMPCA will have a value of nothing and raise a runtime error).

8. The codes then goes to the Do and falls through to the Else and keeps iterating until there is a GMPCA and DAFRPCA match at which time it does 2.

Problem statement;

During the matching process when a new data element is reached two consecutive no matches, I get a; Runtime error 91; ‘Object variable or with block variable not set’. The variable rngGMPCA has no value because a match to variable DAFRPCA could not be found. I have tried to assign a value to GMPCA but I am obviously not using the correct syntax. Match variables (DAFRPCA and GMPCA) on sheets First QT and Prepared Expense.

 

[SkipVought]

You never define rngDestin or rngToCopy.

Your code control structure should be as simple as this.

Sub Macro1()
    With Sheets("First QT").Range("B1:B2034")
        For Each rngDAFRPCA In [DAFRPCA]
            Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
            If Not rngGMPCA Is Nothing Then
              'found
              With rngGMPCA
                r = 1
                Do While (.Offset(r, 0).Value = rngDAFRPCA.Value)
                  'perform whatever
                  r = r + 1
                Loop
                ' -- ITS NOT CLEAR WHAT Source SHOULD BE
                Set rngToCopy = .Range(Source)
                Set rngDestin = Sheets("First QT").Range(Dest)
                rngToCopy.Copy Destination:=rngDestin
              End With
            Else
            'not found -- ITS NOT CLEAR WHAT Dest SHOULD BE
              Sheets("First QT").Range(Dest).Value = "No data retrieved"
            End If
        Next
    End With
End Sub

just need to figure out what those ranges are supposed to be.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

The code below inialized the variables. Do I put this above your With statement?

'This initializes the variable for the first match.
Set rngDAFRPCA = Sheets("Prepared_Expense").Range("Q2").Offset(1, -15) 'Range _
must be in the same row as the start of DAFRPCA data.
Set rngGMPCA = Sheets("First QT").Range("B4") 'Change to variable!
Set rngToCopy = Sheets("First QT").Range("CB4:CE4") 'Change to variable!
Set rngDestin = Sheets("Prepared_Expense").Range("R2")

 

[SkipVought]

dim wsPE as worksheet, wsQT as worksheet
set wsPE = Sheets("Prepared_Expense")
set wsQT = Sheets("First QT")
For Each rngDAFRPCA In range(wsPE.Range("Q2"), wsPE.Range("Q2").End(xlDown))
  
Next
Sub Macro1()
    With Sheets("First QT").Range("B1:B2034")
        For Each rngDAFRPCA In range(wsPE.Range("Q2"), wsPE.Range("Q2").End(xlDown))            
            Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
            If Not rngGMPCA Is Nothing Then
              'found
              With rngGMPCA
                r = 1
                Do While (.Offset(r, 0).Value = rngDAFRPCA.Value)
                  'perform whatever
                  r = r + 1
                Loop
                ' vv should this be inside the Do...Loop ???
                Set rngToCopy = Range(wsQT.Cells(.row, "CB"), wsQT.Cells(.row, "CE")) 
               with wsPE.usedrange
                 Set rngDestin = wsPE.Cells(.Rows.Count + .Row, "R")
                ' ^^ should this be inside the Do...Loop ???
               end with
                rngToCopy.Copy Destination:=rngDestin
              End With
            Else
            'not found -- ITS NOT CLEAR WHAT Dest SHOULD BE
              Sheets("First QT").Range(Dest).Value = "No data retrieved"
               with wsPE.usedrange
                 wsPE.Cells(.Rows.Count + .Row, "R").Value = "No data retrieved"

               end with
            End If
        Next
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Does you code say as long as the GMPCA equals the DAFRPCA Then do what ever is in the Do loop? Which at the moment is nothing. If this is true then the Do loop needs to copy the rngCopyTo to rngDestin and move to the next row. Putting the code where your questions into the loop are does not work. The code runs with no error messages but also no results.

 

[SkipVought]

With rngGMPCA
  r = 1
  Do While (.Offset(r, 0).Value = rngDAFRPCA.Value)

    r = r + 1
  Loop



End with

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

What part of your code does this?

If rngDAFRPCA = rngGMPCA Then
Sheets("First QT").Select
rngToCopy.Select
Selection.Copy
Sheets("Prepared_Expense").Select
rngDestin.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Moves to next row in the expense worksheet and the next destination cell.
Set rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)

'Increments counter.
intCounter = intCounter + 1

or is this what I should put in the Do Loop?

 

[SkipVought]

                ' vv should this be inside the Do...Loop ???
                Set rngToCopy = Range(wsQT.Cells(.row, "CB"), wsQT.Cells(.row, "CE")) 
               with wsPE.usedrange
                 Set rngDestin = wsPE.Cells(.Rows.Count + .Row, "R")
               end with
                rngToCopy.Copy Destination:=rngDestin
                ' ^^ should this be inside the Do...Loop ???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Skip, here is what the code now looks like. I had to change With rngGMPCA to With rngDAFRPCA and (.Offset(r, 0).Value = rngDAFRPCA.Value) to (.Offset(r, 0).Value = rngGMPCA.Value)
for the loop to execute.

The Destin here should be the same as if data was being transfered from wsQT except the string goes there instead.
'not found -- ITS NOT CLEAR WHAT Dest SHOULD BE
Sheets("Prepared_Expense").Range(Dest).Value = "No data retrieved

When I F8 through the sub Object variable or With Block variable is not set error displays when I put the cursor on Set rngDestin = wsPE.Cells(.Rows.Count + .Row, "R").

Sub PCA()
Dim wsPE As Worksheet, wsQT As Worksheet
Set wsPE = Sheets("Prepared_Expense")
Set wsQT = Sheets("First QT")

With Sheets("First QT").Range("B1:B2034")
For Each rngDAFRPCA In Range(wsPE.Range("B2"), wsPE.Range("B2").End(xlDown))
Set rngGMPCA = .Find(rngDAFRPCA, LookIn:=xlValues)
If Not rngGMPCA Is Nothing Then
'found
'With rngGMPCA
With rngDAFRPCA
r = 1
Do While (.Offset(r, 0).Value = rngGMPCA.Value)

Set rngToCopy = Range(wsQT.Cells(.Row, "CB"), wsQT.Cells(.Row, "CE"))
With wsPE.UsedRange
Set rngDestin = wsPE.Cells(.Rows.Count + .Row, "R")
End With
rngToCopy.Copy Destination:=rngDestin
r = r + 1
Loop
End With

Else
'not found -- ITS NOT CLEAR WHAT Dest SHOULD BE
Sheets("Prepared_Expense").Range(Dest).Value = "No data retrieved"
With wsPE.UsedRange
wsPE.Cells(.Rows.Count + .Row, "R").Value = "No data retrieved"

End With
End If
Next
End With
End Sub

 

[SkipVought]

With wsPE.UsedRange
  Set rngDestin = wsPE.Cells(.Rows.Count + .Row, "R")
End With

so in the debigger, Watch Window, what is the value of
[tt]
wsPE.UsedRange.Row
wsPE.UsedRange.Rows.Count
[/tt]
The Set statement is to set rngDestin to column R the FIRST EMPTY ROW -- hence, ...

wsPE.UsedRange.Row + wsPE.UsedRange.Rows.Count

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

I added the code. When I F8 through once wsPE.UsedRange.Row value is 1 and wsPE.UsedRange.Rows.Count value is 13708. When I F8 through a second time wsPE.UsedRange.Rows.Count is incremented by 1 to 13709.

Can't detect any errors but no data transfered from QT to PE.

 

[SkipVought]

A ROW is getting added

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Yes, but there is nothing in it. Do you want to see the code as it exists now?

 

[SkipVought]

With rngDAFRPCA
   r = 1
   Do While (.Offset(r, 0).Value = rngGMPCA.Value)
                  
      Set rngToCopy = Range(wsQT.Cells(.Row, "CB"), wsQT.Cells(.Row, "CE"))

what is in rngDAFRPCA.Row 'cuz there appears to be NUTHIN in rngToCopy???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884