Distributing TYV in table - I think VBA can handle this. Thanks for helping!

pedie · Sep 16, 2010

In the table below i have hilighted four cells in each row.
What I am wanting to do here in that, in the way/patern it is hilighted i want the word 'TYV' to be inserted in the cells where it is hilighted.
My table has 52 rows starting from c2.
I dont want the code to look for the background colour that is just what I am wanting to show how it should go.

I hope this can be done in vba.
Thanks alot for helping!
Pedie

doofusboy · Sep 16, 2010

1. Highlight all the cells you want to receive the 'TYV' value.
2. After all cells are highlighted, simply type TYV
3. Then hit Ctrl and Enter keys simultaneously

MickG · Sep 16, 2010

Hi, Try:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG16Sep51
[COLOR="Navy"]Dim[/COLOR] cols, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
cols = Array(1, 4, 5, 8, 9, 12, 13, 14, 1, 2, 3, 6, 7, 10, 11, 14)
[COLOR="Navy"]For[/COLOR] Rw = 1 To 52
    Ac = IIf(Ac = UBound(cols) + 1, 0, Ac)
    Range(Cells(Rw, cols(Ac)), Cells(Rw, cols(Ac + 1))).Offset(1, 2).value = "TYV"
    Ac = Ac + 2
    [COLOR="Navy"]If[/COLOR] Ac = 8 [COLOR="Navy"]Then[/COLOR] Rw = Rw - 1
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

pedie · Sep 16, 2010

Mick thanks alot!

Lemmi try it out. gimmi a minute!

pedie · Sep 16, 2010

Mick, i have no idea how you did that...even looking @ the code i dont know how it works

But it works perfect. This is going to make aprt of my work very easy now.
MIck, thank you so much!
Problem solved.
I want to also ask how this code works but i think if you are busy then....it'll still be okay

Thank you, thank you thank you.

pedie · Sep 16, 2010

Is this possible? Now we do insert TYV
4times in a row, or 2in the beginning and 2 in the end (other end)
Can we insert TXC for 1st two cells then on the other two
TYV??

I'm sorry if I am asking too much now

pedie · Sep 16, 2010

Can someone in here please kindly explain to me how this code works if you have some idea

???

I need to know how this works..!

Thanks alot!

MickG · Sep 17, 2010

Hi, Below is the Code , Altered to Add you Extra text on the first 2 cells.
The code is really rather basic, and I'm sure it could be improved.
At the top of the code is the array "Cols".
The code loops through 52 rows, at each row it looks at the array "Cols"
In pairs, so that on the first sheet row, The variable "Ac" is 0, (In a function array the first element is 0) so the range of cells in the first sheet row are Specified by the first 2 values in the array "Cols" i.e 1 and 4 , this represents Column 1 and 4.
That range is then filled with the Text "TYV".
The code loops to the second row, the counter (The "AC" variable) then increments 2, to get the next 2 Elements in the array, Which Are 5 and 8, These Represents the Range ,Second row columns 5 to 8.
The code continues as above , but on Row 4 there are 2 cells at the begining and 2 cells at the end of the Row.
So when we reach line 4 the code places the text in the last 2 cells, Now the next 2 cells are on the same line but at the begining, so the code then lncrements backward i.e rw = rw -1. This ensure that row 4 has 2 lots of text ,2 at the begining of the row and 2 at the end.
When the Code has run through the Array "Cols". the following line
"Ac = IIf(Ac = UBound(cols) + 1, 0, Ac)"
starts it of at 1 agan and will do this continually for as many rows as you like.
NB:- On the Line "Range(-----, the Offset property is use to position the code at the correct starting point. i.e. "C2"

Code:

[COLOR=navy]Sub[/COLOR] MG17Sep41
[COLOR=navy]Dim[/COLOR] cols, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] Str [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
cols = Array(1, 4, 5, 8, 9, 12, 13, 14, 1, 2, 3, 6, 7, 10, 11, 14)
Str = "TYV"
[COLOR=navy]For[/COLOR] Rw = 1 To 53
    Ac = IIf(Ac = UBound(cols) + 1, 0, Ac)
    Range(Cells(Rw, cols(Ac)), Cells(Rw, cols(Ac + 1))).Offset(1, 2).value = Str
    Ac = Ac + 2
    [COLOR=navy]If[/COLOR] Ac = 8 [COLOR=navy]Then[/COLOR]
        Rw = Rw - 1
        Str = "TXC"
    [COLOR=navy]Else[/COLOR]
        Str = "TYV"
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Rw
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

pedie · Sep 17, 2010

Mick, I dont know if you're still around but thanks a million for coming back and explaining everything to me on how it works

.
I'm reading it through again and again but i know it'll take someitme for me to learn this thing

one small prob. from my table below.
the last row here is reflecting correctly but not the first 3 rows.
i have shown manually the expected resulted.

when u come back please take a look again okay, Mick!

Thanks agaiin, thanks a milllion!
in the mean time let me read through again and try to solved the BIG Puzzle for me....

pedie · Sep 17, 2010

Also i particularly don't understand the part of the code mentioned below..'m new to vba

Code:

Ac = IIf(Ac = UBound(cols) + 1, 0, Ac)

Distributing TYV in table - I think VBA can handle this. Thanks for helping!

pedie

Well-known Member

Excel Facts

doofusboy

Well-known Member

MickG

MrExcel MVP

pedie

Well-known Member

pedie

Well-known Member

pedie

Well-known Member

pedie

Well-known Member

MickG

MrExcel MVP

pedie

Well-known Member

pedie

Well-known Member

Similar threads

Forum statistics

Share this page

Distributing TYV in table - I think VBA can handle this. Thanks for helping!

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock