Macro leaves row blank rather than updating

C

Catyclaire85

New Member
Joined
Nov 23, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi, hope you can point out my error.

The code I have attached is to find a row of data and update it. When I step through it the code works to identify the row and what should go there but when it finishes running the row found is left blank. What have I done/missed please?


VBA Code: 
Sub IAUpdates()

Dim Output(1 To 10000, 1 To 28) As Variant
Dim ArraytoLookup As Variant
Dim ValtoLookup As Variant
Dim Rowfound As Integer
Dim OutCol As Integer ‘start column for Shifts table sheet1
Dim Details As Variant

Details = Sheet1.Range(“A1:AB28”)
                            ArraytoLookup = Sheet2.Range(“A1:A10000”)
                            ValtoLookup = Sheet1.Range(“C5”)  ‘IA Title to locate in Sheet 2 to update
                            Rowfound = IsInArrayNumbers(ArraytoLookup, ValtoLookup)

If Rowfound > 0 Then
Output(Rowfound, 2) = Sheet1.Range(“K4”)
Output(Rowfound, 3) = Sheet1.Range(“C7”)
Output(Rowfound, 4) = Sheet3.Range(“A11”)
Output(Rowfound, 5) = Sheet1.Range(“E7”)
Output(Rowfound, 6) = Sheet1.Range(“C9”)
Output(Rowfound, 7) = Sheet1.Range(“E9”)
Output(Rowfound, 8) = Sheet1.Range(“C11”)
Output(Rowfound, 10) = Sheet1.Range(“F11”)
    If Sheet1.Range(“F11”) = “Completed” Then
    Output(Rowfound, 9) = Sheet3.Range(“A13”)
    End If

Output(Rowfound, 11) = Sheet1.Range(“H9”)
Output(Rowfound, 12) = Sheet1.Range(“B14”)
    If Sheet1.OptionButton1 = True Then
    Output(Rowfound, 13) = “Impact”
    Else
    If Sheet1.OptionButton2 = True Then
    Output(Rowfound, 13) = “Benefit”
    End If
    End If

Output(Rowfound, 14) = Sheet1.Range(“X17”)
Output(Rowfound, 24) = Sheet1.Range(“Z6”)
Output(Rowfound, 25) = Sheet1.Range(“Z8”)
Output(Rowfound, 26) = Sheet1.Range(“Z10”)
Output(Rowfound, 27) = Sheet1.Range(“Z12”)
Output(Rowfound, 28) = Sheet1.Range(“Z14”)
End If

Sheet2.Range(“A” & Rowfound & “:AB” & Rowfound) = Output
End Sub

Function IsInArrayNumbers(arr As Variant, valueToFind) As Variant
‘ searches for a number within a provided array
‘ best for searches in one dimensional arrays
IsInArrayNumbers = 1
IsInArrayNumbers = Application.Match(valueToFind, arr, 0)
    If IsError(IsInArrayNumbers) Then IsInArrayNumbers = -1


End Function
 
Sort by date Sort by votes
Alex Blakenburg said:
Firstly can you move the line below to be before the last "End If"
VBA Code: 
Sheet2.Range("A" & Rowfound & ":AB" & Rowfound) = Application.Index(Output, Rowfound, 0)
It is currently executing that line even when Rowfound returns -1 (ie lookup value is not found)

That should be causing a different error to to the one you are getting but try that first.
Click to expand...
Done that. No error is happening but the rowfound is then left blank
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Then it is not finding the value.
Do you know how to use the immediate window ?

Add the debug.print line where I have it in the code below and show me what it prints to the immediate window. If you can't see it ctrl+G will open it.
If Rowfound = -1 then see if you can manually find what it shows as being in Sheet1 C5 in Sheet 2 column A

VBA Code: 
Details = Sheet1.Range("A1:AB28")
                            ArraytoLookup = Sheet2.Range("A1:A10000")
                            ValtoLookup = Sheet1.Range("C5")  'IA Title to locate in Sheet 2 to update
                            Rowfound = IsInArrayNumbers(ArraytoLookup, ValtoLookup)
                            
Debug.Print "C5= "; Sheet1.Range("C5"); vbTab; "Rowfound= "; Rowfound

If Rowfound > 0 Then
 
Upvote 0
Alex Blakenburg said:
Then it is not finding the value.
Do you know how to use the immediate window ?

Add the debug.print line where I have it in the code below and show me what it prints to the immediate window. If you can't see it ctrl+G will open it.
If Rowfound = -1 then see if you can manually find what it shows as being in Sheet1 C5 in Sheet 2 column A

VBA Code: 
Details = Sheet1.Range("A1:AB28")
                            ArraytoLookup = Sheet2.Range("A1:A10000")
                            ValtoLookup = Sheet1.Range("C5")  'IA Title to locate in Sheet 2 to update
                            Rowfound = IsInArrayNumbers(ArraytoLookup, ValtoLookup)
                           
Debug.Print "C5= "; Sheet1.Range("C5"); vbTab; "Rowfound= "; Rowfound

If Rowfound > 0 Then
Click to expand...
Thanks for telling me how to access the immediate window - that is new to me. Result is below:
 

Attachments

  • Debug Print.png
    Debug Print.png
    23.6 KB · Views: 6
Upvote 0
Can you confirm that when you click on the debug button on the message box it is definitely the Application.Index line that is erroring out ?

I can give you my revised code but it shouldn't have any impact on that message.

VBA Code: 
Sub IAUpdates_V02()

Dim Output(1 To 28) As Variant
Dim ArraytoLookup As Variant
Dim ValtoLookup As Variant
Dim Rowfound As Integer
Dim OutCol As Integer 'start column for Shifts table sheet1
Dim Details As Variant

Details = Sheet1.Range("A1:AB28")
                            ArraytoLookup = Sheet2.Range("A1:A10000")
                            ValtoLookup = Sheet1.Range("C5")  'IA Title to locate in Sheet 2 to update
                            Rowfound = IsInArrayNumbers(ArraytoLookup, ValtoLookup)
                           
Debug.Print "C5= "; Sheet1.Range("C5"); vbTab; "Rowfound= "; Rowfound   ' Remove when finished testing

If Rowfound > 0 Then
    Output(2) = Sheet1.Range("K4")
    Output(3) = Sheet1.Range("C7")
    Output(4) = Sheet3.Range("A11")
    Output(5) = Sheet1.Range("E7")
    Output(6) = Sheet1.Range("C9")
    Output(7) = Sheet1.Range("E9")
    Output(8) = Sheet1.Range("C11")
    Output(10) = Sheet1.Range("F11")
    If Sheet1.Range("F11") = "Completed" Then
        Output(9) = Sheet3.Range("A13")
    End If
   
    Output(11) = Sheet1.Range("H9")
    Output(12) = Sheet1.Range("B14")
    If Sheet1.OptionButton1 = True Then
        Output(13) = "Impact"
    Else
        If Sheet1.OptionButton2 = True Then
            Output(13) = "Benefit"
        End If
    End If
   
    Output(14) = Sheet1.Range("X17")
    Output(24) = Sheet1.Range("Z6")
    Output(25) = Sheet1.Range("Z8")
    Output(26) = Sheet1.Range("Z10")
    Output(27) = Sheet1.Range("Z12")
    Output(28) = Sheet1.Range("Z14")
   
    Sheet2.Range("A" & Rowfound & ":AB" & Rowfound) = Output
End If

End Sub
 
Last edited:
Upvote 0
Solution
Alex Blakenburg said:
Can you confirm that when you click on the debug button on the message box it is definitely the Application.Index line that is erroring out ?

I can give you my revised code but it shouldn't have any impact on that message.

VBA Code: 
Sub IAUpdates_V02()

Dim Output(1 To 28) As Variant
Dim ArraytoLookup As Variant
Dim ValtoLookup As Variant
Dim Rowfound As Integer
Dim OutCol As Integer 'start column for Shifts table sheet1
Dim Details As Variant

Details = Sheet1.Range("A1:AB28")
                            ArraytoLookup = Sheet2.Range("A1:A10000")
                            ValtoLookup = Sheet1.Range("C5")  'IA Title to locate in Sheet 2 to update
                            Rowfound = IsInArrayNumbers(ArraytoLookup, ValtoLookup)
                           
Debug.Print "C5= "; Sheet1.Range("C5"); vbTab; "Rowfound= "; Rowfound   ' Remove when finished testing

If Rowfound > 0 Then
    Output(2) = Sheet1.Range("K4")
    Output(3) = Sheet1.Range("C7")
    Output(4) = Sheet3.Range("A11")
    Output(5) = Sheet1.Range("E7")
    Output(6) = Sheet1.Range("C9")
    Output(7) = Sheet1.Range("E9")
    Output(8) = Sheet1.Range("C11")
    Output(10) = Sheet1.Range("F11")
    If Sheet1.Range("F11") = "Completed" Then
        Output(9) = Sheet3.Range("A13")
    End If
   
    Output(11) = Sheet1.Range("H9")
    Output(12) = Sheet1.Range("B14")
    If Sheet1.OptionButton1 = True Then
        Output(13) = "Impact"""
    Else
        If Sheet1.OptionButton2 = True Then
            Output(13) = "Benefit"""
        End If
    End If
   
    Output(14) = Sheet1.Range("X17")
    Output(24) = Sheet1.Range("Z6")
    Output(25) = Sheet1.Range("Z8")
    Output(26) = Sheet1.Range("Z10")
    Output(27) = Sheet1.Range("Z12")
    Output(28) = Sheet1.Range("Z14")
   
    Sheet2.Range("A" & Rowfound & ":AB" & Rowfound) = Output
End If

End Sub
Click to expand...
Yes the bug is in the application.index line of code.

I have copied your updated code and it has worked like a charm. Thank you so much - I also didn't know that I could just put Output(column number) I thought it always had to be column and row. This is fabulous!! Thank you for all your patience and help with this.
 
Upvote 0
Glad we were able to get there in the end. Thank you for the feedback.

PS: don't forget to get rid of the debug.print line now that you have it working.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Find, copy and paster multiple rows based on multiple search items in single column
Replies
7
Views
536
DanteAmor
DanteAmor
JohnPoole
  • Solved
Delete rows based on unique values
2
Replies
17
Views
1K
kevin9999
K
S
  • Solved
Display data of Specific Sheets on Listbox Userform
2
Replies
11
Views
1K
Akuini
Akuini
B
  • Question Question
Array attached to Dictionary Not Updating...
Replies
2
Views
547
bradmsg
B
C
  • Question Question
Using macro to copy row above just formulas and leave everything else blank
Replies
0
Views
461
Chubba
C

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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