Another Easy Question

[MovingBeyondtheRecordButton]

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

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.

 

[Mike H]

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.

 

[Mike H]

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.

 

[MovingBeyondtheRecordButton]

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.

 

[Don Guillett]

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 address removed)
"MovingBeyondtheRecordButton"

 

[MovingBeyondtheRecordButton]

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]

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]

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

 

[MovingBeyondtheRecordButton]

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]

Hi,

No you didn't mention you needed to pass that to an SQL query. Is this what
you want?

Sub sonic()
Dim F As Range
Set sht = Sheets("Sheet1")
For Each F In ActiveSheet.Range("A4:A40, F4:F40")
If F.Value <> "" Then
Num = 1000 & F.Value
'Run SQL query using NUM?
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.

 

[Mike H]

I meant

Sub sonic()
Dim F As Range
Set sht = Sheets("Sheet1")
For Each F In sht.Range("A4:A40, F4:F40")
If F.Value <> "" Then
Num = 1000 & F.Value
'Run SQL query using NUM?
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.

 

[John Bundy]

I'm quite aware of how to reference sheets, but if you rewrite everyones code
your own way they:
1) don't learn anything, and
2) you have to follow up with, at this point, at least 3 posts trying to
explain why yours is better.
I adjusted his code in 1 response and he could then see his own fault in the
range selection.

Perhaps I incorrectly assume that most people who post here are novices
trying to learn, or just want their code corrected to work.

 

[John Bundy]

And sorry it may just be the mood, but from you sig:
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Sounds like you did a lot of assuming rather than sufficiently answering the
question.

 

[MovingBeyondtheRecordButton]

Thank you...I learned a lot through your various post. And your last post
was the answer to my question.

 

[MovingBeyondtheRecordButton]

I decide to use:

For Each F In Worksheets("Sheet1").Range("A4:A40,F4:F40")
If F.Value <> "" Then
Num = 1000 & F.Value
'Then take Num and run Sql Query

 

[MovingBeyondtheRecordButton]

By name I as referring to Num in the post above.

I decide to use:

For Each F In Worksheets("Sheet1").Range("A4:A40,F4:F40")
If F.Value <> "" Then
Num = 1000 & F.Value
'Then take Num and run Sql Query

 

[Mike H]

Glad i could help and thanks for the feedback
--
Mike

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

 

[Don Guillett]

However, if you have text or the "dreaded space bar" in the cell then you
will still get 1000 ......

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"MovingBeyondtheRecordButton"

 

[mybuypassports mybuypassports]

Our team is a unique producer of quality fake documents.
We offer only original high-quality fake passports, driver's licenses,
stamps and other products for following countries:
USA, Australia, Belgium, Brazil, Canada, Finland, France, Germany,
Israel, Mexico, Netherlands, South Africa, Spain, United Kingdom.

To get the additional information and place the order just visit our
website:
========================================================================
==================================================
(If in some technical reasons you are unable to visit our website we are
always happy to answer your questions on email addresses mentioned
below)
========================================================================
==================================================
http://www.mynewpass.com



General support: (e-mail address removed)


Technical support: (e-mail address removed)


-----------------------------
Keywords:

sell false/fake passports of Afghanistan
sell false/fake passports of Albania
sell false/fake passports of Algeria
sell false/fake passports of Andorra
sell false/fake passports of Angola
sell false/fake passports of Antigua & Deps
sell false/fake passports of Argentina
sell false/fake passports of Armenia
sell false/fake passports of Australia
sell false/fake passports of Austria
sell false/fake passports of Azerbaijan
sell false/fake passports of Bahamas
sell false/fake passports of Bahrain
sell false/fake passports of Bangladesh
sell false/fake passports of Barbados
sell false/fake passports of Belarus
sell false/fake passports of Belgium
sell false/fake passports of Belize
sell false/fake passports of Benin
sell false/fake passports of Bhutan
sell false/fake passports of Bolivia
sell false/fake passports of Bosnia Herzegovina
sell false/fake passports of Botswana
sell false/fake passports of Brazil
sell false/fake passports of Brunei
sell false/fake passports of Bulgaria
sell false/fake passports of Burkina
sell false/fake passports of Burundi
sell false/fake passports of Cambodia
sell false/fake passports of Cameroon
sell false/fake passports of Canada
sell false/fake passports of Cape Verde
sell false/fake passports of Central African Rep
sell false/fake passports of Chad
sell false/fake passports of Chile
sell false/fake passports of China
sell false/fake passports of Colombia
sell false/fake passports of Comoros
sell false/fake passports of Congo
sell false/fake passports of Congo {Democratic Rep}
sell false/fake passports of Costa Rica
sell false/fake passports of Croatia
sell false/fake passports of Cuba
sell false/fake passports of Cyprus
sell false/fake passports of Czech Republic
sell false/fake passports of Denmark
sell false/fake passports of Djibouti
sell false/fake passports of Dominica
sell false/fake passports of Dominican Republic
sell false/fake passports of East Timor
sell false/fake passports of Ecuador
sell false/fake passports of Egypt
sell false/fake passports of El Salvador
sell false/fake passports of Equatorial Guinea
sell false/fake passports of Eritrea
sell false/fake passports of Estonia
sell false/fake passports of Ethiopia
sell false/fake passports of Fiji
sell false/fake passports of Finland
sell false/fake passports of France
sell false/fake passports of Gabon
sell false/fake passports of Gambia
sell false/fake passports of Georgia
sell false/fake passports of Germany
sell false/fake passports of Ghana
sell false/fake passports of Greece
sell false/fake passports of Grenada
sell false/fake passports of Guatemala
sell false/fake passports of Guinea
sell false/fake passports of Guinea-Bissau
sell false/fake passports of Guyana
sell false/fake passports of Haiti
sell false/fake passports of Honduras
sell false/fake passports of Hungary
sell false/fake passports of Iceland
sell false/fake passports of India
sell false/fake passports of Indonesia
sell false/fake passports of Iran
sell false/fake passports of Iraq
sell false/fake passports of Ireland {Republic}
sell false/fake passports of Israel
sell false/fake passports of Italy
sell false/fake passports of Ivory Coast
sell false/fake passports of Jamaica
sell false/fake passports of Japan
sell false/fake passports of Jordan
sell false/fake passports of Kazakhstan
sell false/fake passports of Kenya
sell false/fake passports of Kiribati
sell false/fake passports of Korea North
sell false/fake passports of Korea South
sell false/fake passports of Kosovo
sell false/fake passports of Kuwait
sell false/fake passports of Kyrgyzstan
sell false/fake passports of Laos
sell false/fake passports of Latvia
sell false/fake passports of Lebanon
sell false/fake passports of Lesotho
sell false/fake passports of Liberia
sell false/fake passports of Libya
sell false/fake passports of Liechtenstein
sell false/fake passports of Lithuania
sell false/fake passports of Luxembourg
sell false/fake passports of Macedonia
sell false/fake passports of Madagascar
sell false/fake passports of Malawi
sell false/fake passports of Malaysia
sell false/fake passports of Maldives
sell false/fake passports of Mali
sell false/fake passports of Malta
sell false/fake passports of Marshall Islands
sell false/fake passports of Mauritania
sell false/fake passports of Mauritius
sell false/fake passports of Mexico
sell false/fake passports of Micronesia
sell false/fake passports of Moldova
sell false/fake passports of Monaco
sell false/fake passports of Mongolia
sell false/fake passports of Montenegro
sell false/fake passports of Morocco
sell false/fake passports of Mozambique
sell false/fake passports of Myanmar, {Burma}
sell false/fake passports of Namibia
sell false/fake passports of Nauru
sell false/fake passports of Nepal
sell false/fake passports of Netherlands
sell false/fake passports of New Zealand
sell false/fake passports of Nicaragua
sell false/fake passports of Niger
sell false/fake passports of Nigeria
sell false/fake passports of Norway
sell false/fake passports of Oman
sell false/fake passports of Pakistan
sell false/fake passports of Palau
sell false/fake passports of Panama
sell false/fake passports of Papua New Guinea
sell false/fake passports of Paraguay
sell false/fake passports of Peru
sell false/fake passports of Philippines
sell false/fake passports of Poland
sell false/fake passports of Portugal
sell false/fake passports of Qatar
sell false/fake passports of Romania
sell false/fake passports of Russian Federation
sell false/fake passports of Rwanda
sell false/fake passports of St Kitts & Nevis
sell false/fake passports of St Lucia
sell false/fake passports of Saint Vincent & the Grenadines
sell false/fake passports of Samoa
sell false/fake passports of San Marino
sell false/fake passports of Sao Tome & Principe
sell false/fake passports of Saudi Arabia
sell false/fake passports of Senegal
sell false/fake passports of Serbia
sell false/fake passports of Seychelles
sell false/fake passports of Sierra Leone
sell false/fake passports of Singapore
sell false/fake passports of Slovakia
sell false/fake passports of Slovenia
sell false/fake passports of Solomon Islands
sell false/fake passports of Somalia
sell false/fake passports of South Africa
sell false/fake passports of Spain
sell false/fake passports of Sri Lanka
sell false/fake passports of Sudan
sell false/fake passports of Suriname
sell false/fake passports of Swaziland
sell false/fake passports of Sweden
sell false/fake passports of Switzerland
sell false/fake passports of Syria
sell false/fake passports of Taiwan
sell false/fake passports of Tajikistan
sell false/fake passports of Tanzania
sell false/fake passports of Thailand
sell false/fake passports of Togo
sell false/fake passports of Tonga
sell false/fake passports of Trinidad & Tobago
sell false/fake passports of Tunisia
sell false/fake passports of Turkey
sell false/fake passports of Turkmenistan
sell false/fake passports of Tuvalu
sell false/fake passports of Uganda
sell false/fake passports of Ukraine
sell false/fake passports of United Arab Emirates
sell false/fake passports of United Kingdom
sell false/fake passports of United States
sell false/fake passports of Uruguay
sell false/fake passports of Uzbekistan
sell false/fake passports of Vanuatu
sell false/fake passports of Vatican City
sell false/fake passports of Venezuela
sell false/fake passports of Vietnam
sell false/fake passports of Yemen
sell false/fake passports of Zambia
sell false/fake passports of Zimbabwe

*** Sent via Developersdex http://www.developersdex.com ***