I tried your code and it changed the submission numbers in my range to
include the preface 1000 but...maybe I didn't explain myself...I don't want
see the number with the 1000 in front in each cell but rather the sql
database needs the longer form of the submission number in order to run the
query. That is why I wanted to give this new number the name Num and use Num
in my query.

On a side note: The other thing that was strange when I ran your code was
cell F4 has the 1000 placed in front of it twice. Originally F4=39480 Now
F4=1000100039480

"Mike H" wrote:

Hi,

I don't understand what you mean by

and give this new number a name.

But this macro puts the 1000 in front of each number

Sub sonic()
Dim F As Range
Set sht = Sheets("Sheet1")
For Each F In sht.Range("A4:A40")

If F.Value < "" Then
F.Value = 1000 & F.Value
End If

If F.Offset(, 5).Value < "" Then
F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value
End If
Next
End Sub


Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"MovingBeyondtheRecordButton" wrote:

How do I take the value from each cell defined in the range("A4:A40,F4:F40")
and place 1000 before the value and give this new number a name.

Example:
Number in A4 is 37984
I want to use 100037984

I have tried...

Dim F As Range
Dim myNum As Variant
Dim Num As Long
Worksheets("Sheet1").Activate
For Each F In Range("A4:A40", "F4:F40")
myNum = F.Value
Num = myNum & 1000