Automatically Increase Cell Value +1

[ir121973]

I wonder whether someone may be able to help me please with a problem I have in using an auto incrementing number.

I'm using the code below to copy and paste data between worksheets.

Sub CopyData() 
Dim ws1 As Worksheet 
Dim ws2 As Worksheet 
Dim rng1 As Range 
Set ws1 = Sheets("Input") 
Set ws2 = Sheets("Output") 
On Error Resume Next 
Set rng1 = ws1.Range(ws1.[b4], ws1.Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlConstants) 
On Error GoTo 0 
If rng1 Is Nothing Then Exit Sub 
Application.ScreenUpdating = False 
Set rng2 = ws2.[b2] 
rng1.Copy 
rng2.PasteSpecial xlPasteValues 
'copy column I to Output C2 
rng1.Offset(0, 7).Copy 
rng2.Offset(0, 1).PasteSpecial xlPasteValues 
'copy column N to Output d2 
rng1.Offset(0, 12).Copy 
rng2.Offset(0, 2).PasteSpecial xlPasteValues 
rng2.Offset(0, 3).Resize(rng1.Cells.Count, 1) = "Scheduled Site" 
Application.CutCopyMode = False 
Application.ScreenUpdating = True 
End Sub

This works fine, but if possible, what I'd like to do is where the code inserts the word 'Scheduled Site' I'd like it to add the number for which it appears in the list e.g. for the first row in the list to say 'Scheduled Site 1', then 'Scheduled Site 2' automatically calculating the next number until it reaches the end of the list.

I must admit this has proved to be beyond my capabilites. I just wondered whether somone could perhaps show me what I need to do to add the auto incrementing number.

Many thanks

 

[francomusso]

First you need a variable...
Dim myCount As Integer
myCount = 1

To add that variable's value to an existing cell's entry, use & to concatenate (join) the two...

Range("B6").Value = Range("B6").Value & " " & myCountingVariable

(please note the reason that I have used & twice is that I wanted to add a space between the two pieces of info rather than have something like Scheduled Site1)

So ...in your example, that would be something like
rng2.Offset(0, 3).Resize(rng1.Cells.Count, 1) = "Scheduled Site" & " " & myCountingVariable

I hope that helps!
All the best,
Franco Musso

 

[francomusso]

Please note that I made a mistake in my last post, in that the variable I set hada different name to the variable I referenced later on - use this example instead!...

First you need a variable...
Dim myCount As Integer
myCount = 1

To add that variable's value to an existing cell's entry, use & to concatenate (join) the two...

Range("B6").Value = Range("B6").Value & " " & myCount

(please note the reason that I have used & twice is that I wanted to add a space between the two pieces of info rather than have something like Scheduled Site1)

So ...in your example, that would be something like
rng2.Offset(0, 3).Resize(rng1.Cells.Count, 1) = "Scheduled Site" & " " & myCount
I hope that helps!
All the best,
Franco Musso

 

[ir121973]

Hi Franco, many thanks for replying to my post.

I've added your code to mine and I can get the number to appear, but it isn't auto incrementing i.e. they all say 'Scheduled Site 1'. Have you any ideas please.

Kind regards

 

[francomusso]

I'd usually add that into a looping macro so that each time it completes, I add one on to the variable like so:

myCount = myCount + 1

Please could you explain the scope of the macro a little further. Will it always copy date from and to the same places each time?... and is the "scheduled site" text only being entered once int he workbook / each time the macro is run - e.g. first time you run the macro it'll say "scheduled site 1", the next time the same cell will be updated to "scheduled site 2", etc?

I'll look into it further as soon as I understand a little more...

 

[ir121973]

Hi,

Yes the macro will copy the data from the 'Input' sheet to the 'Output' Sheet with the columns staying the same, but the number of rows of data will change.

The 'Scheduled Site' text will only be entered once, i.e. I'll add the data into the 'Input sheet' and then I'll click the button to perform the copy and paste whcih includes the aforementioned text.

I hope this helps.

Kind regards

 

[francomusso]

Sorry, I'd totally misunderstood!
As its not strictly a number that's being incremented, its part of an existing string of text, I'd have the macro extract the right-most characters (the number), increment that by one, and then combine "scheduled site " and the new number in the new cell like so...

Range("B1").Select
lastNumber = Mid(ActiveCell.Value, 15, 4)
Range("B1").Value = "Scheduled Site " & lastNumber + 1

That worked perfectly for me incrementing on an existing value of "Scheduled Site 1" in a specific cell B1.

I hope you're able to adapt that for your situation. Will the scheduled site text always be in the same cell?

 

[ir121973]

Hi, many thanks for your update and apologies are not needed. I didn't explain it as well as I could have.

I must admit the solution isn't quite what I'd hoped because I had hoped it would put the text in automatically without the need to have a piece of text in the first cell.

I'll work with this.

Many thanks for your time and trouble and kind regards