adding to code - Dev Shed

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

August 2nd, 2003, 11:35 AM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

adding to code

How do you add to this code so next time it will incert and copy the next row down.

Private Sub CommandButton2_Click()
Rows("24:24").Select
Selection.Insert Shift:=xlDown
Range("B25:F25").Select
Selection.Copy
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

so some how it would be

Private Sub CommandButton2_Click()
Rows("25:25").Select
Selection.Insert Shift:=xlDown
Range("B26:F26").Select
Selection.Copy
Range("B25").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

August 2nd, 2003, 01:56 PM

computer

echo $usertitle['computer'];

Dev Shed God 4th Plane (6500 - 6999 posts)

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

try this untested code:

at the top do:

Code:

dir varindex as integer
dir varindex2 as integer
dir rangea as string
dir rangeb as string
dir rangec as string

then:

Code:

Private Sub Form_Load()
 varindex = 1
End Sub

Private Sub CommandButton2_Click() 
varindex2 = varindex + 1
Rows("varindex:varindex").Select 
Selection.Insert Shift:=xlDown 
rangea = "B" & varindex2
rangeb = "F" & varindex2
Range("rangea:rangeb").Select 
Selection.Copy
rangec = "B" & varindex
Range("rangec").Select 
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 
varindex=varindex + 1
End Sub

btw, that will start at row 1 - change:

Private Sub Form_Load()
varindex = 1
End Sub

to reflect your starting row (24?)

__________________

Last edited by computer : August 2nd, 2003 at 01:58 PM.

August 3rd, 2003, 05:29 AM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

Thx, trying to get it working.

When I insert the following code

dir varindex as integer
dir varindex2 as integer
dir rangea as string
dir rangeb as string
dir rangec as string

it comes up in red and when u run it , it comes up with error. I know ive got to put the values in but where?, and how are thay writen? ( very very much a beginner) :-).

Thanx for your help

Mark

August 3rd, 2003, 07:21 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

Code:

dir varindex as integer
dir varindex2 as integer
dir rangea as string
dir rangeb as string
dir rangec as string

must go at the VERY TOP of your form's module (I am assuming you are using VBA?), they go before any other subs or anthing unless there are already some there in which case put them at the end of the others.

Then paste in this bit:

Code:

Private Sub Form_Load()
 varindex = 1
End Sub

Private Sub CommandButton2_Click() 
varindex2 = varindex + 1
Rows("varindex:varindex").Select 
Selection.Insert Shift:=xlDown 
rangea = "B" & varindex2
rangeb = "F" & varindex2
Range("rangea:rangeb").Select 
Selection.Copy
rangec = "B" & varindex
Range("rangec").Select 
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 
varindex=varindex + 1
End Sub

When you load the form, it will set the CommandButton2 sub to start its selecting and moving at row 1, to make it start at a different row, change varindex = 1 in the form_load sub to reflect the number of the row to start at.

Then when you run it, each time you click the button, it should move down each row.

Hope that helps,
computer

August 4th, 2003, 03:00 AM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

Nope still doesnt work but. Ive enclose a pic of what im doing.

Attached Images

code error.jpg (52.4 KB, 127 views)

August 4th, 2003, 03:38 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

sorry, i typed 'dir' by accident and the copy & pasted the error. change the 'dir' to 'dim'

August 5th, 2003, 12:25 AM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

The first bit seems ok now , but now its comeing up with an error ferther down

Attached Images

error.jpg (43.4 KB, 116 views)

August 5th, 2003, 03:18 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

try removing the quotes around the varindexes

August 5th, 2003, 03:27 AM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

Nope syntax error........

#10

August 5th, 2003, 03:33 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

hmm. let me try this on my excel...

#11

August 5th, 2003, 03:44 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

Yes i see what you mean. Let me shove this to the VB forums.

#12

August 5th, 2003, 01:22 PM

allmad

Registered User

Join Date: Jul 2003

Posts: 17

Time spent in forums: 47 sec
Reputation Power: 0

thanx for your help in this

#13

August 6th, 2003, 09:02 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

To be hones, I didn't really read what you were trying 2 achieve, I'm just fixing Computer's code

Code:

If varindex = 0 Then
    varindex = 1
End If
varindex2 = varindex + 1
Rows(varindex & ":" & varindex).Select
Selection.Insert Shift:=xlDown
rangea = "B" & varindex2
rangeb = "F" & varindex2
Range(rangea & ":" & rangeb).Select
Selection.Copy
rangec = "B" & varindex
Range(rangec).Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
varindex = varindex + 2

Remember that you must add 2, bcs a line has already been inserted.

And I just added the if statement, bcs I got an error when I stopped the code, and run it again (bcs varindex = 0)

The rest speaks 4 itself. Shout if you still have a prob.

[edit]
p.s. this is in the onclick event
[/edit]

#14

August 6th, 2003, 10:04 AM

computer

echo $usertitle['computer'];

Join Date: Jan 2003

Location: UK

Posts: 6,673

Time spent in forums: 4 Weeks 9 h 2 m 19 sec
Reputation Power: 219

it goes in 'sub commandbutton2_click()' btw and i had an if statement for running the code but didn't paste it in!