Simple macro, but learning VBA as I go, so really having some trouble

[MSkog]

Any help at all would be appreciated.

My understanding is that this macro should be simple, but with no VBA background I'm learning by doing. Unfortunately I hit a roadblock that I don't think I can get over on my own.


What I'm trying to do: On Sheet1, in Column C, there are lists of projects/subprojects. If they are subprojects of a project, C identifies the subproject, not the project (e.g. Subproject J-2 instead of Project J). The macro I am trying to write checks the values in Column C against a list of subprojects and outputs the project name (not the subproject) in Column D; if it doesn't match the list of subprojects then it outputs whatever the value is in column C. The list of subprojects is in another sheet, identified as "Input", due to the structure of the workbook.


This is obviously incomplete, but the error location is bolded below, and I wrote the text of the error after the code.

***

Dim Report As Worksheet
Dim Inputsheet As Worksheet
Dim i As Integer
Dim lastRow As Integer
Dim c As Integer
Dim d As Integer
Dim rng As range

Set Report = Excel.Worksheets("Sheet1")
Set Inputsheet = Excel.Worksheets("Input")


lastRow = Report.UsedRange.Rows.Count



Application.ScreenUpdating = False


Set rng = Inputsheet.range("A24:A28")


For i = 2 To lastRow
If Report.Cells(i, 3).Value <> "" Then 'omits blank cells
If InStr(1, Inputsheet.range(rng).Value, Report.Cells(i, 1).Value, vbTextCompare) > 0 Then
Report.Cells(i, 1).Value = Inputsheet.Cells(c, d).Value
Exit For
Else
Report.Cells(i, 1).Value = Report.Cells(i, 3)
End If
End If
Next i


Application.ScreenUpdating = True


End Sub




<method 'range'="" of="" object="" '_worksheet'="" failed=""><method 'range'="" of="" object="" '_worksheet'="" failed=""><method 'range'="" of="" object="" '_worksheet'="" failed="">Error is "Inputsheet.range(rng).Value=Method 'Range' of object '_Worksheet' failed<method 'range'="" of="" object="" '_worksheet'="" failed="">. </method>


Thank you again.</method></method></method>

 

[iliauk]

You use the command:

[COLOR=#333333]Set rng = Inputsheet.range("A24:A28")

And then

[/COLOR]

[COLOR=#333333]If InStr(1, [/COLOR]Inputsheet.range(rng).Value

Which can be expanded to:

[COLOR=#333333]If InStr(1, [/COLOR]Inputsheet.range([COLOR=#333333]Inputsheet.range("A24:A28")[/COLOR]).Value

(By substitution) Hence you are not supplying the correct range and getting an error.

I would suggest changing the InString command to:

[B][COLOR=#333333]If InStr(1,[/COLOR]rng.Value
[/B]

 

[JoeMo]

In your bold part change:

Inputsheet.range(rng).Value

to this:
rng.Value

you have already set the range variable so it can be used directly as a range object.