Appending specific text at the end of every row depending on occurrence of a number

[manvit]

Hi,
I m new to VBA and my problem is explained below with example
i need to go from this :
[TABLE="width: 100"]
<tbody>[TR]
[TD]rank[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]jon[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]alex[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]william[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]josh[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]jamies[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]sarah[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]angela[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]haley[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]julie[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]sofia[/TD]
[/TR]
</tbody>[/TABLE]
In this table , i need to append a text which has no column name. 'A' has to be appended the end of every row until the next occurrence of 1 in column rank . from the second occurrence of 1 in rank column i need 'B' to be append at the end of every row.
after applying the code, it should look like this.
[TABLE="width: 100"]
<tbody>[TR]
[TD]rank[/TD]
[TD]name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]jon[/TD]
[TD] A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]alex[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]william[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]josh[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]jamies[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]sarah[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]angela[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]haley[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]julie[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]sofia

[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

Any help on writing this code will be very much appreciated.

Thanks,
Manvit

 

[MickG]

Try this for results in column "C"

[COLOR="Navy"]Sub[/COLOR] MG01Nov12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
c = 64
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Temp = Chr(c)
    [COLOR="Navy"]End[/COLOR] If
    Dn.Offset(, 2).Value = Temp
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[manvit]

Hi Mick, thanks for taking time to write this. can you please explain the code ?

 

[manvit]

Thanks It works !!! . i need to know how did you manage to print 'B' ? In reality i need to append text like "Physics", "Chem". how can i append these?

 

[MickG]

No problem , Have a look at the below:-

[COLOR="Navy"]Sub[/COLOR] MG01Nov40
'[COLOR="Green"][B]Variables Declared[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

'[COLOR="Green"][B]"Rng", Sets the range of data in column "A" to be looped through[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

'[COLOR="Green"][B]NB:- The Ascii codes from "65 to 90", give[/B][/COLOR]
'[COLOR="Green"][B]you a list of 'Characters "A to Z", so Chr(65") = "A"[/B][/COLOR]
c = 64

'[COLOR="Green"][B]Loop through column "A"[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  
  '[COLOR="Green"][B]When dn.value = 1 then c = 65 and Chr(65)= "A"[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] Dn.Value = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        
        '[COLOR="Green"][B]So on first loop when "1" is found temp (string Variable) = "A", then[/B][/COLOR]
        '[COLOR="Green"][B]Next time Dn.value = "1" , c = 66 and Temp = "B" ect[/B][/COLOR]
        Temp = Chr(c)
    [COLOR="Navy"]End[/COLOR] If
    
    '[COLOR="Green"][B]Column "C" value = Temp[/B][/COLOR]
    Dn.Offset(, 2).Value = Temp
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Try this:-
Change array "Subjects" as shown in Code !!

[COLOR="Navy"]Sub[/COLOR] MG01Nov42
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

'[COLOR="Green"][B]Change the array below to Your specific subject[/B][/COLOR]
ray = Array("Physics", "Chem", "Maths", "English")


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Temp = ray(c - 1)
    [COLOR="Navy"]End[/COLOR] If
    Dn.Offset(, 2).Value = Temp
[COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

http://www.dec.org.uk/

 

[manvit]

No problem , Have a look at the below:-

[COLOR=Navy]Sub[/COLOR] MG01Nov40
'[COLOR=Green][B]Variables Declared[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]

'[COLOR=Green][B]"Rng", Sets the range of data in column "A" to be looped through[/B][/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

'[COLOR=Green][B]NB:- The Ascii codes from "65 to 90", give[/B][/COLOR]
'[COLOR=Green][B]you a list of 'Characters "A to Z", so Chr(65") = "A"[/B][/COLOR]
c = 64

'[COLOR=Green][B]Loop through column "A"[/B][/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
  
  '[COLOR=Green][B]When dn.value = 1 then c = 65 and Chr(65)= "A"[/B][/COLOR]
    [COLOR=Navy]If[/COLOR] Dn.Value = 1 [COLOR=Navy]Then[/COLOR]
        c = c + 1
        
        '[COLOR=Green][B]So on first loop when "1" is found temp (string Variable) = "A", then[/B][/COLOR]
        '[COLOR=Green][B]Next time Dn.value = "1" , c = 66 and Temp = "B" ect[/B][/COLOR]
        Temp = Chr(c)
    [COLOR=Navy]End[/COLOR] If
    
    '[COLOR=Green][B]Column "C" value = Temp[/B][/COLOR]
    Dn.Offset(, 2).Value = Temp
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks Mick ,
But it wouldn't work if i need to append words like 'Physics' and 'chemistry'. So do you have any idea about this ?

 

[manvit]

Genius!!!! Thanks a lot Mick, very helpful. have a great day

 

[MickG]

You're welcome