VBA Help

[ststern45]

Hi everyone,

I have a list of 3160 "Pairs". e.g. 1,2 1,3 up to the last pair 79,80

This list is located in cell range EE1 through EF3160o, worksheet "Draws"

What I would like to know if this is possible:

1. Copy the 1st "Pair" 1 and 2 (cell EE1 & EF1) to the same worksheet "Draws" cell AD3:AE3

2. Copy the information from cell range AF3:AG3 to another worksheet "Pairs".


Then next pair 1,3:

1. Copy the 2nd "Pair" 1 and 3 (cell EE2 & EF2) to the same worksheet "Draws" cell AD3:AE3

2. Copy the information from cell range AF4:AG4 to another worksheet "Pairs".

Continue this process until the very last pair 79,80 which is a total of 3,160 "Pairs" are calculated.


Thanks in advance!!

 

[kevin lazell]

hello ststern
i am having a look at this for you
your 1st pair is being copied to ad3:ae3
your 2nd pair is also being copied to ad3:ae3
is this correct or should the second pair go to ad4:ae4

you say to copy af3:ag3 to worksheet pairs, where abouts do you want it to go
and is it just those cells or is it the same as the other ones where you then want af4:ag4 copied etc
let me know please and i will write the code for you

cheers

kevin

 

[kevin lazell]

hi ststern
try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x, y, z As Variant
x = 2: z = 2
Do
x = x + 1: y = y + 1: z = z + 1
Range("ad" & z) = Range("ee" & y): Range("ae" & z) = Range("ef" & y)
Sheets("pairs").Range("af" & x) = Range("af" & x)
Sheets("pairs").Range("ag" & x) = Range("ag" & x)
If y = 3160 Then Exit Sub
Loop
End Sub


if have kept the range the same when copying to sheet "pairs"
you may need to alter it to suit
hope this is what you need
cheers
kevin

 

[ststern45]

Thanks for your reply. I just arrived home from work and noticed your reply.

I did not provide enough information.
Sorry!!

I have a list of 3160 "Pairs". e.g. 1,2 1,3 up to the last pair 79,80

This list is located in cell range EF1 through EG3160, in my worksheet called "Draws".

What I would like to know if this is possible:
Step 1 of 3160

1. Copy the 1st "Pair" 1 and 2 (cell EF1 & EG1) to the same worksheet called "Draws" to cell AD3:AE3. Once the 2 numbers that make up the pair e.g. 1 & 2 are copied to AD3:AE3 there are 2 calculations that take place in cell range AF3:AG3.

2. Next copy the results from cell range AF3:AG3 to another worksheet called "PairHitSkip" starting in cell range E20:F20


Step 2 of 3160

1. Copy the 2nd "Pair" 1 and 3 (cell EF2 & EG2) to the same worksheet called "Draws" to cell AD3:AE3. Once the 2 numbers that make up the pair e.g. 1 & 3 are copied to AD3:AE3 there are 2 calculations that take place in cell range AF3:AG3.

2. Next copy the results from cell range AF3:AG3 to another worksheet "PairHitSkip" starting in cell range E21:F21.

Continue this process until the very last pair 79,80 which is a total of 3,160 "Pairs" are calculated.


Thanks in advance!!

Again sorry for the missing steps.

hello ststern
i am having a look at this for you
your 1st pair is being copied to ad3:ae3
your 2nd pair is also being copied to ad3:ae3
is this correct or should the second pair go to ad4:ae4

you say to copy af3:ag3 to worksheet pairs, where abouts do you want it to go
and is it just those cells or is it the same as the other ones where you then want af4:ag4 copied etc
let me know please and i will write the code for you

cheers

kevin

 

[kevin lazell]

ADVERTISEMENT

hi ststern
here is what we have so far.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x, y As Variant
x = 19
Do
x = x + 1: y = y + 1
Range("ad3") = Range("ef" & y): Range("ae3") = Range("eg" & y)
Sheets("PairHitSkip").Range("e" & x) = Range("af3").Value
Sheets("PairHitSkip").Range("f" & x) = Range("ag3").Value
If y = 3161 Then Exit Sub
Loop
End Sub

what calculations do you have in cells af3:ag3 ?
i think it may be better if we incorporated it into the code

cheers

kevin

 

[ststern45]

Cell AF3 Formula

=COUNTIF(AA1:AA1000,"H")

Cell AG3 Formula:

=VLOOKUP("H",OFFSET(AA1:AA1000,0,0,1000,3),3,FALSE)

 

[kevin lazell]

hi ststern

try the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x, y As Variant
x = 19
Do
x = x + 1: y = y + 1
Range("ad3") = Range("ef" & y): Range("ae3") = Range("eg" & y)
Range("af3") = Application.WorksheetFunction.CountIf(Range("AA1:AA1000"), "H")
Sheets("PairHitSkip").Range("e" & x) = Range("af3").Value
Range("ag3") = Application.WorksheetFunction.VLookup("H", Offset(Range("AA1:AA1000"), 0, 0, 1000, 3), 3, False)
Sheets("PairHitSkip").Range("f" & x) = Range("ag3").Value
If y = 3161 Then Exit Sub
Loop
End Sub

may pay to try this on a copy of your workbook just in case

good luck

kevin

 

[ststern45]

Thanks Kevin for all your help.

I'll palce this in this morning.