Another Easy Question
in [Programming]
|
Prev: Link checker: checking if a URL exists
Next: Debugging
From: MovingBeyondtheRecordButton on 26 Mar 2010 10:43 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 "Don Guillett" wrote: > 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 > dguillett(a)gmail.com > "MovingBeyondtheRecordButton" > <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message > news:A486B6A6-9EAD-4ECA-A640-392FB8119773(a)microsoft.com... > > 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 > > > > > > > > . >
From: MovingBeyondtheRecordButton on 26 Mar 2010 10:48 By name I as referring to Num in the post above. "MovingBeyondtheRecordButton" wrote: > 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 > > > "Don Guillett" wrote: > > > 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 > > dguillett(a)gmail.com > > "MovingBeyondtheRecordButton" > > <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message > > news:A486B6A6-9EAD-4ECA-A640-392FB8119773(a)microsoft.com... > > > 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 > > > > > > > > > > > > > . > >
From: Mike H on 26 Mar 2010 10:48 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. "MovingBeyondtheRecordButton" wrote: > Thank you...I learned a lot through your various post. And your last post > was the answer to my question. > > "Mike H" wrote: > > > 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. > > > > > > "MovingBeyondtheRecordButton" wrote: > > > > > 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 > > > > > > > > > > > > > > >
From: Don Guillett on 26 Mar 2010 14:53 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 dguillett(a)gmail.com "MovingBeyondtheRecordButton" <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message news:05A7D95A-CEC7-406A-89AE-888224BDAF03(a)microsoft.com... >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 > > > "Don Guillett" wrote: > >> 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 >> dguillett(a)gmail.com >> "MovingBeyondtheRecordButton" >> <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message >> news:A486B6A6-9EAD-4ECA-A640-392FB8119773(a)microsoft.com... >> > 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 >> > >> > >> > >> >> . >>
From: Shane Devenshire on 26 Mar 2010 18:09
Hi, I appologize if this has been covered, since I didn't read all the responses. No macro necessary: 1. Enter 1000 in a blank cell and copy that cell 2. Select the range A4:A40, F4:F40 and choose Edit, Paste Special, Multiply You can record that and it will run far faster than a loop. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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 > > > |