Solved: refer to a named range in input box?

[jazznaura]

hi all,

just a quick question,

came i change the cell address part of the input box message so it display the name of the range.

thanks


Sub COPYTODATA()
Dim Cell As Range, Source As Range
Dim Response As String


Set Source = Sheets("input").Range("weeknumber, P2, tpnd, J5, P5, K8, C11, I11, C13, description, I13, M13, P13, E23, date, P23, E26, names, shift, C30, F57")

If Application.WorksheetFunction.CountA(Source) < Source.Cells.Count Then


For Each Cell In Source.SpecialCells(xlCellTypeBlanks)
Response = InputBox("Please enter the DATA missing from " & Cell.Address(0, 0))
If StrPtr(Response) = 0 Then Exit Sub
If Response = vbNullString Then COPYTODATA
Cell.Value = Response
Next
End If

Sheets("input").Select
Range("a1").Select

End Sub

[mikerickson]

Dim rangeName as String

Rem code
For Each Cell In Source.SpecialCells(xlCellTypeBlanks)

rangeName = Cell.Address(0, 0)
On Error Resume Next
rangeName = Cell.Name.Name: Rem yes two
On Error GoTo 0

Response = InputBox("Please enter the DATA missing from " & rangeName)

Rem code

This will only work if there is a one cell named range equal to Cell, since a cell can be in more than one named range,

[jazznaura]

hi mikerickson,

thanks for the reply.
have used your code and works great.

cheers,

jazznaura.



Sub COPYTODATA()
Dim Cell As Range, Source As Range
Dim Response As String
Dim rangeName As String

Set Source = Sheets("input").Range("weeknumber, datead, apma, tpnd, fffff, goal, valuex")

If Application.WorksheetFunction.CountA(Source) < Source.Cells.Count Then

For Each Cell In Source.SpecialCells(xlCellTypeBlanks)
rangeName = Cell.Address(0, 0)
On Error Resume Next
rangeName = Cell.Name.Name:
On Error GoTo 0
Response = InputBox("Please enter the DATA missing from " & rangeName)
If StrPtr(Response) = 0 Then Exit Sub
If Response = vbNullString Then COPYTODATA
Cell.Value = Response

Next
End If

Sheets("input").Select
Range("a1").Select