VBA Code - can someone give me some tips

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
Below is one of my first attempts of actually writing my own code. I really want to get my head around this, so I thought I would throw it around to those of you who "Know" this stuff.
Can someone who understands VBA look through the following code, and possibly give me a bit of a tip.

Just to put you in the picture....in Column D, I have about 30 codes running downwards: FF, OE, and blank.
What i want is to assign a new code in Column B with a consecutive number, and the code itself joined together.
So.....

If D1 = "FF"
then I B1 = 1FF
if D2 = "FF"
then B2 = 2FF
if D3 = "OE"
then D2 = 1OE
etc....

The code I came up with is:
(however, I am not sure how to make the code count upwards - i keep getting 1's).


Sub ff()
x = 0
y = 0
Do
If ActiveCell = "" Then End
If ActiveCell = "FF" Then
ActiveCell.Offset(0, -2).Value = x + 1 & "FF"
Else
If ActiveCell = "OE" Then
ActiveCell.Offset(0, -2).Value = y + 1 & "OE"

Else
ActiveCell.Offset(0, -2).Value = " "
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You just need to increment x and y like this:

Code:
Sub ff() 
x = 0 
y = 0 
Do 
   If ActiveCell = "" Then End 
   If ActiveCell = "FF" Then 
      ActiveCell.Offset(0, -2).Value = x + 1 & "FF" 
      x = x + 1
   ElseIf ActiveCell = "OE" Then 
      ActiveCell.Offset(0, -2).Value = y + 1 & "OE" 
      y = y + 1
   Else 
      ActiveCell.Offset(0, -2).Value = " " 
   End If 
   ActiveCell.Offset(1, 0).Select 
Loop 
End Sub
 

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
Your a legend Andrew,

don't know if i would have found that one myself....but now I understand the concept.

Thanks Again,
 

Forum statistics

Threads
1,144,768
Messages
5,726,179
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top