Posts by How How

Nilem, Yup that works very well... I wont pretend to full understand the code but I guess I will have to have a play with it in order to get my head round it.

Thank you very much!!

Hi, I am working on a project and am stuck on a problem regarding entering "Bye's" into a set range of letters. The bye's position will be determined row 22 of the column. I have created a macro that will do it individually but it would be a lot of codes for all the separate columns. I have attached an example which will hopefully show what I'm trying to achieve. I know I could enter the byes manually but.... That wouldn't be as much fun..... + I hope to do a larger project once I have the small one working properly. P.s The code is in the example sheet

Wow, both codes work as designed and the timings are very similar........ Both much less code than I have just now...... Thank you both very much........ All on X-mas day too....... Great help and hopefully I will learn more from this.

With both codes I had to remove the numbers from row 20 because the data was being put below them, but this is not a hardship as I can move the numbers to another location. (Numbers were for weekly bye's)

I will go and work with this, I will be trying to add a "Bye" in the column where each team will have to have a bye when there is an odd number (Week 1, week 2 etc).... But I will have a go myself & if I do get stuck I will start another thread. Thanks again :rock:

Jolivanes, Merry XMas...... Your code is putting everything in col C (Originally asked for)..... See the picture below, all letter "A" pairing will now start in "H2", Letter "B" pairing will start in "I2" and so on. I needed to copy Trebor76 code 17 times changing the col & letter each time... Works fine, I was just wondering if this could be shortened.
P.s The numbers on row 20 are for Byes to be applied in case of odd numbers (The numbers will have to be manually inputted and will be variable).
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Example.JPG","caption":"Caption","data-attachmentid":1197326}[/ATTACH]

Trebor76... I have done what I was requiring by doing as below (Many times)... So that All letter "A" pairs will be in Col H, all letter "B" pairs will be in Col I and so on...... Working perfectly this way, just wondered if there was a shorter code to do the same...... No probs if cant be done easily.

For Each rngMyCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)  'Works from cell B2 down. Change to suit if necessary.




    '**********************************************************************************************************************************


        If Left(rngMyCell, 1) = Range("H1") Then  'Entry in cell H1 is not case sensitive. Refer above.
            Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = Right(rngMyCell, 1)




        ElseIf Right(rngMyCell, 1) = Range("H1") Then
            Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = Left(rngMyCell, 1)


           End If


     '**********************************************************************************************************************************




  If Left(rngMyCell, 1) = Range("I1") Then
            Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Value = Right(rngMyCell, 1)




        ElseIf Right(rngMyCell, 1) = Range("I1") Then
            Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Value = Left(rngMyCell, 1)




            End If


     '**************************************************************************************************************************

Excellent stuff guys..... And yes a very merry x-mas and an even happier NEWYEAR to you all :wowee::cheers:

Works Perfetctly!! I noticed there were random "spaces" between the 2 letters in my column but the code still worked...... Thanks very much!! :congrats:

I Know I didnt specify in the original post (Didnt think of it till now).... Would it be possible to do the same but with mulitple columns... ie Letter A in col H2, Letter "B" in col I2, Letter "C" in Col then the all fill out with thier pairing letter. No problems if it cant be done easy...... I can just use a seperate code for each letter. Thanks again

Hi, I'm looking for help with extracting a single letter from cells, Col B2 (B1 & C1 are headers). In the example below the extracted data is in column C and is taken from every cell in column B that has a letter "D" in that range. (Can be left or right of letter D), the actual search letter will come from another cell (H2). Note there are no blanks in the extracted data (Col C). I have tried over a week to no avail. Thanks in advance.
[TABLE="border: 2, cellpadding: 1, width: 100"]

Col B

Col C

AD

A

FG

F

RT

S

DF

ER

SD

AE

[/TABLE]

I'm very interested in VBA Loops but have come up against a small but annoying problem.

I'm Basically looping a range on sheet 2 for non blanks then moving the non blanks to sheet 1.

The issue is the range between the 2 sheets are different (Much less non blanks than blanks)

On sheet 1 I have multiple ranges and they overwrite the range below as the code is using the full range in sheet 2

So that said sheet 2 loop i = 3 to 54 But for sheet 1 I just want to use 3 to 23 ..... Code below

Sub Test()


Dim i As Integer, j As Integer
Dim Mysheet As Worksheet, myOtherSheet As Worksheet


Set Mysheet = Sheet2
Set myOtherSheet = Sheet1




With Sheet2


  Ndt = Application.Range("Testing").Cells.SpecialCells(xlCellTypeConstants).Count


  End With


  If Ndt > 16 Then


MsgBox "Too Many names!!"


Call Clear


Exit Sub








Else


End If


Call Clear




j = 3
For i = 3 To 54


If Mysheet.Cells(i, 2).Value <> "" Then
      myOtherSheet.Cells(j, 2).Value = Mysheet.Cells(i, 2).Value
      j = j + 1




End If
Next i










End Sub

Re: Move Data from multiple columns to 2 columns

KJBox, I hope I'm not too late...... I have triple checked this for duplicate matches and there are none..... Different format now but same rules (6 teams = 3 games / 5 rounds & 7 teams = 4 games / 7 rounds)
So, basically, all the columns with "ho" in row 3 should be put into a new sheet starting at C3 & All "Aw" starting D3...... See pics below

[ATTACH=CONFIG]71233[/ATTACH]

[ATTACH=CONFIG]71234[/ATTACH]

Re: Move Data from multiple columns to 2 columns

:0ops:......... Oh no....... I just realised the code I used for doing the "draw" does not work.... looks good, but doesn't work.... there are duplicate games (in the odd games & even games), oh well back to the drawing board for me. I suppose I could manually have the letters sorted for every scenario from 4 teams to 20 teams & paste them in like my example sheet & let your code do the rest. Just when I thought I was nearly there!!!

Thanks for all your help..... It's much appreciated

Re: Move Data from multiple columns to 2 columns

Excellent...... Works perfect..... Thanks for all the help :thumbcoo:

Re: Move Data from multiple columns to 2 columns

RjBox, the issue with the missing round is now fixed.... Thanks. But the odd number of teams isn't, I added the odd example below (7 teams = 7 rounds..... But 8 columns)... Sorry to be a pain!!

forum.ozgrid.com/index.php?attachment/71218/

Re: Move Data from multiple columns to 2 columns

Quote from KjBox;784587

Is there always an even number of teams?

No but my sheet will put in a BYE instead of a letter, so 7 teams = 7 rounds as below

[TABLE="width: 583"]

Round

A

B

C

D

E

F

G

Bye

Rnd 1

Bye

G

F

E

D

C

B

A

Rnd 2

C

Bye

A

G

F

E

D

B

Rnd 3

E

D

Bye

B

A

G

F

C

Rnd 4

G

F

E

Bye

C

B

A

D

Rnd 5

B

A

G

F

Bye

D

C

E

Rnd 6

D

C

B

A

G

Bye

E

F

Rnd 7

F

E

D

C

B

A

Bye

G

[/TABLE]

Re: Move Data from multiple columns to 2 columns

Guys,
I've attached a 2nd example test sheet (just noticed the last update is short of 1 round), I have put buttons on the sheet to call up 6, 10 & 20 teams so any adjusted code can be tested.

Example of rounds
6 teams = 5 rounds
10 teams = 9 rounds
20 teams = 19 rounds

Thanks

Re: Move Data from multiple columns to 2 columns

KjBox, looks like I was little early....... There is a problem when there is more than 8 teams (Could be between 4 & 20 teams, so that would be a possible 19 rounds), it hangs up

y(iii, 1) = x(i, 1)

There is a team counter in cell D3 on sheet 1 if that would help make it dynamic?

Re: Move Data from multiple columns to 2 columns

KjBox, Yup, that did the trick.... Thanks so much :spin:

Gigsmo, I would be interested in your code if that could be adapted to give the same output (I would like to try & have a play with both codes to try & "learn" a bit more)

Re: Move Data from multiple columns to 2 columns

Sorry guys, I should have stated that I have data in col A & B on sheet 1 that I am getting an error with.

For i = 2 To UBound(x, 1)
        y(iii, 1) = x(i, 1)
        For ii = 2 To UBound(x, 2) Step 2
            y(iii, 2) = x(i, ii): y(iii, 3) = x(i, ii + 1)
            iii = iii + 1
        Next

Re: Move Data from multiple columns to 2 columns

KjBox, That gives the correct output, thanks...... Can the code ignore data in col A & B?...... I have data in there & the code hangs up if there is data that 2 columns.........

Re: Move Data from multiple columns to 2 columns

Gigsmo, It does check for a numeric value & that part works fine, thanks...... However it is still returning the wrong pairings & too many games if you check pic 4.jpg, that is what it should return with 8 teams , there should only be 4 games in each round when there are 8 teams, Note: Rnd 1 is in row 8....... & the first game is H G, then F E etc

This stems from my original mistake.... Sorry! I hope you can help because this would be really helpful.... Thanks in advance