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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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,181,532
Messages
5,930,453
Members
436,740
Latest member
Ogidi

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