Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > To adapt macro for relative lines
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
PST PST is offline
external usenet poster
  
Posts: 29
Default To adapt macro for relative lines
hello

To adapt this part of macro below for relative lines

For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

Thank You

Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next




J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons à l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
Cells(Lig, 20) = "toto" & " " & Lig

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default To adapt macro for relative lines
for cell A1

For J = 1 To 8

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

Next

"PST" wrote:

hello

To adapt this part of macro below for relative lines

For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

Thank You

Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next




J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
Cells(Lig, 20) = "toto" & " " & Lig

End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
PST PST is offline
external usenet poster
  
Posts: 29
Default To adapt macro for relative lines
Thank you for you reply but
red line in vb editor

for cell A1


Joel a écrit :
for cell A1

For J = 1 To 8

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

Next

"PST" wrote:

hello

To adapt this part of macro below for relative lines

For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

Thank You

Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next




J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
Cells(Lig, 20) = "toto" & " " & Lig

End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default To adapt macro for relative lines
I think the problem is the line wrapping. make sure code is on one line or
use _ for line continuation.

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

"PST" wrote:

Thank you for you reply but
red line in vb editor

for cell A1


Joel a écrit :
for cell A1

For J = 1 To 8

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

Next

"PST" wrote:

hello

To adapt this part of macro below for relative lines

For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

Thank You

Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next




J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
Cells(Lig, 20) = "toto" & " " & Lig

End Sub



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
PST PST is offline
external usenet poster
  
Posts: 29
Default To adapt macro for relative lines
I give you the whole code for test if possibele, to put some rows of
numbers in feuil3 A:H run macro_Combs

I tried to find, sent messages, nobody gave me the answer


With these three macros,I would like the result feuil3,
by taking the series of figures of the feuillle feuil3 A1:H200


test_Insert_Rows()
Combin_6N()
macro_Combs()

I would like that this part holds account of active line


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next



My procedu
1) run test_Insert_Rows() for inserer 28 blank line between each line of
feuil3
2) run macro_Combs() which calls Combin_6N() all the 28 lines and puts
the combinations
which correspond to the rows of the line in progress in the 28 following
rows.
Thus if I have 100 series of numbers, I would have 100 rows X 28
combinations by lines
2800 rows of different combinations.



Option Explicit
Public Lig As Long
Public Col As Long
Public numRows As Integer

Option Base 1


1)
Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub

2)

Sub test_Insert_Rows()
Dim r As Integer
Sheets("Base").Activate
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 28
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
End Sub

3)

Sub macro_Combs()

For Lig = 1 To 100 Step 28
For Col = 1 To 1

Next
Cells(Lig, 17).Select
Call Combin_6N
Next
End Sub




Joel a écrit :
I think the problem is the line wrapping. make sure code is on one line or
use _ for line continuation.

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

"PST" wrote:

Thank you for you reply but
red line in vb editor

for cell A1


Joel a écrit :
for cell A1

For J = 1 To 8

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=0).Value

Next

"PST" wrote:

hello

To adapt this part of macro below for relative lines

For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next

Thank You

Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 8

vN(J) = Sheets("feuil3").Cells(J).Value 'J avant

Next




J = J - 1

ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
Cells(Lig, 20) = "toto" & " " & Lig

End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change order of drop lines relative to the plotted data inExcel 2007 chart icystorm Excel Discussion (Misc queries) 0 April 1st 10 03:48 PM
Adapt hiding columns? PG Oriel Excel Programming 1 November 15th 06 11:12 AM
how can i adapt this formula? Gary Keramidas[_2_] Excel Programming 5 July 15th 05 08:51 PM
adapt macro monty Excel Worksheet Functions 3 March 9th 05 12:44 PM
Please help me to adapt this code. Rob Hargreaves Excel Programming 1 February 2nd 05 08:27 PM


All times are GMT +1. The time now is 05:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement