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

First, you aren't telling your program which sheet the range is in:
Worksheets("Sheet1").Activate
For Each F In Range("A4:A40", "F4:F40")

should be
Worksheets("Sheet1").Activate
For Each F In ActiveSheet.Range("A4:A40", "F4:F40")

If it still doesn't work with the formatting, you may have to change Num to
a string.
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


"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
>
>
>

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
>
>
>

John,

It's better to avoid selection altogether by using something like

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

Then it doesn't matter which sheet is active but more importantly

For Each F In ActiveSheet.Range("A4:A40", "F4:F40")

doesn't work because it will include all the columns between A and F in the
range.
--
Mike

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


"John Bundy" wrote:

> First, you aren't telling your program which sheet the range is in:
> Worksheets("Sheet1").Activate
> For Each F In Range("A4:A40", "F4:F40")
>
> should be
> Worksheets("Sheet1").Activate
> For Each F In ActiveSheet.Range("A4:A40", "F4:F40")
>
> If it still doesn't work with the formatting, you may have to change Num to
> a string.
> --
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what
> is helpful.
>
>
> "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
> >
> >
> >

Thanks a bunch...Now I just have to figue out how to slow it down long enough
for it to make the calculations before moving on to the next item in the loop.

"John Bundy" wrote:

> First, you aren't telling your program which sheet the range is in:
> Worksheets("Sheet1").Activate
> For Each F In Range("A4:A40", "F4:F40")
>
> should be
> Worksheets("Sheet1").Activate
> For Each F In ActiveSheet.Range("A4:A40", "F4:F40")
>
> If it still doesn't work with the formatting, you may have to change Num to
> a string.
> --
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what
> is helpful.
>
>
> "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
> >
> >
> >

I have no idea what you mean by "give a name". You would NOT want to name
each cell.

Sub addto()
dim f as range
For Each f In Worksheets("Sheet30").Range("A4:A40", "F4:F40")
If Len(Application.Trim(f)) > 0 And IsNumeric(f) Then
f.Value = 1000 & f.Value
End If
Next f
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"MovingBeyondtheRecordButton"
<(E-Mail Removed)> wrote in message
news:A486B6A6-9EAD-4ECA-A640-(E-Mail Removed)...
> 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
>
>
>

I should have written the range as Range("A4:A40,F4:F40") since I am trying
to use the numbers listed in columns A and F cells 4 through 40.

What I meant by give it a name is...I used the name "Num" to represent the
number all the way down through the macro. So basically, I want to define
this number created after putting the 1000 in front and call it "Num".

"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
> >
> >
> >

Hi,

I agree that Range("A4:A40,F4:F40") would work but in my solution I did it
differently by using offset. I still don't understand what you mean by 'Num'
unless you create an array a variable can only have 1 value.

You loop through the range using the range object F. In my solution I used
F.value and while this is good practice using value isn't necessary because
value is the default property of a range object so we could get away with

For Each F In sht.Range("A4:A40")
If F <> "" Then
F = 1000 & F
End If


To assign the value of F to a variable put 1000 in front of it and then
write ot back to the cell is simply not necessary and adds needless lines of
code.
--
Mike

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


"MovingBeyondtheRecordButton" wrote:

> I should have written the range as Range("A4:A40,F4:F40") since I am trying
> to use the numbers listed in columns A and F cells 4 through 40.
>
> What I meant by give it a name is...I used the name "Num" to represent the
> number all the way down through the macro. So basically, I want to define
> this number created after putting the 1000 in front and call it "Num".
>
> "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
> > >
> > >
> > >

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
> >
> >
> >

I don't want to assign it back to the cell. I want to put the 1000 in front
of each F in the range and assign this number to the name Num and use Num as
the input for my sql query.

"Mike H" wrote:

> Hi,
>
> I agree that Range("A4:A40,F4:F40") would work but in my solution I did it
> differently by using offset. I still don't understand what you mean by 'Num'
> unless you create an array a variable can only have 1 value.
>
> You loop through the range using the range object F. In my solution I used
> F.value and while this is good practice using value isn't necessary because
> value is the default property of a range object so we could get away with
>
> For Each F In sht.Range("A4:A40")
> If F <> "" Then
> F = 1000 & F
> End If
>
>
> To assign the value of F to a variable put 1000 in front of it and then
> write ot back to the cell is simply not necessary and adds needless lines of
> code.
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "MovingBeyondtheRecordButton" wrote:
>
> > I should have written the range as Range("A4:A40,F4:F40") since I am trying
> > to use the numbers listed in columns A and F cells 4 through 40.
> >
> > What I meant by give it a name is...I used the name "Num" to represent the
> > number all the way down through the macro. So basically, I want to define
> > this number created after putting the 1000 in front and call it "Num".
> >
> > "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
> > > >
> > > >
> > > >