Macro leaves row blank rather than updating

[Catyclaire85]

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?

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

 

[Alex Blakenburg]

I am not sure why you are doing it that way but in terms of your immediate issue try replacing your last line with the below:
Note: You are not putting anything in Output(Rowfound, 1), which means you are blanking out your original key value in column A which seems unwise.

Sheet2.Range("A" & Rowfound & ":AB" & Rowfound) = Application.Index(Output, Rowfound, 0)

 

[Catyclaire85]

I am not sure why you are doing it that way but in terms of your immediate issue try replacing your last line with the below:
Note: You are not putting anything in Output(Rowfound, 1), which means you are blanking out your original key value in column A which seems unwise.

Sheet2.Range("A" & Rowfound & ":AB" & Rowfound) = Application.Index(Output, Rowfound, 0)

Hi,
The solution presented throws out a type mismatch error.

How would you do this if you were writing the code from scratch please? I am still very new to VBA.

Thanks
Cat

 

[Alex Blakenburg]

I am not sure what your language setting is and have never seen curly quote marks in the code before but I had to convert all you curly quotes to straight quotes.
Try taking my line and reversing what I had to do. That is, change my straight quotes to your normal "before" and "after" curly quotes.

Once we get that sorted then in terms of a different way of doing it.....
Are you doing some sort of loop or only running it on 1 value in C5 each time ?
If it is just 1 items at a time then using and 10,000 row array doesn't add any value.

I would probably use Find to get the row but as long as you always nominate the range in sheet2 as starting in Row 1, your IsInArrayNumbers will do what you need.

 

[Catyclaire85]

I am not sure what your language setting is and have never seen curly quote marks in the code before but I had to convert all you curly quotes to straight quotes.

I think that happened because I pasted it into word as I had to email myself as can’t post to forums on work computers

 

[Alex Blakenburg]

In that case either copy it into an Excel sheet (assuming it doesn't happen there) or into Notepad.
If you have done the same in reverse for the line I sent you then it is the opposite problem and you need the line to have straight quotes.

 

[Catyclaire85]

I

In that case either copy it into an Excel sheet (assuming it doesn't happen there) or into Notepad.
If you have done the same in reverse for the line I sent you then it is the opposite problem and you need the line to have straight quotes.

I typed your solution in being careful to cross check it. I tried three times to make sure there were no errors.

 

[Alex Blakenburg]

If when you hit debug it is definitely stopping on that line, try sending me a picture of that line. Maybe including at least the previous line of code and the one after it.

Otherwise can you email yourself the Excel file and post the code you have now straight from Excel ?

 

[Catyclaire85]

Hi,

So an update on this - I created a separate excel with just test examples in and the code worked fine but when I tried it again in the actual document I am using it doesn't work.

I have attached the code from the actual document as an image.

 

[Alex Blakenburg]

Firstly can you move the line below to be before the last "End If"

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.