• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Increment References in One of the Cell.

anuwers

Member
Dear Sir,

I need a VBA code to increase the references in one of the cell.

Example;
i am filling various fields of the excel"sheet1" and running VBA macro to share the details in another sheet"Sheet2" as row. this is done through VBA macro
In my sheet 1 - cell B4, I need the references to appear as B001 and for every run of above macro, i need the B4 cell to change as B002.... and so on with every run of VBA macro.

Note: since this B001 is getting recorded in sheet 2, you can use of this to get the next reference in Sheet1 cell b4

Please support in providing the Macro VBA code for this.

Thanks
Anwar
 
Add this to your existing code at the beginning
Code:
Dim cnt as long, NewCnt as string

cnt = Right(sheets("Sheet2").Range("A1"),3)  'Change this address to where B001 is stored in Sheet2

cnt = cnt +1

NewCnt = "B" & cnt

Sheets("Sheet1").range("B4") = NewCnt
 
Dear Alan,

Thanks for your response.

May be i am delivering the message wrongly.

Dear Sir,

I need a VBA code to increase the references in one of the cell.

Example;
i am filling various fields of the excel"sheet1" and running VBA macro to share the details in another sheet"Sheet2" as row. this is done through VBA macro
In my sheet 1 - cell B4, I need the references to appear as B001 and for every run of above macro, i need the B4 cell to change as B002.... and so on with every run of VBA macro.

Note: since this B001 is getting recorded in sheet 2, you can use of this to get the next reference in Sheet1 cell b4

Please support in providing the Macro VBA code for this.

What i get with your macro is : my B4 cell in Sheet1 changes with B2 as reference, but it does not change when i do next update. I need the B4 cell to change as B3 on next udpate. .... and so on..

Let me explain more in details (Note: I changed the reference and cell to make more understanding)

On sheet1 E4 cell i will write 1st time reference as MREC1, this should get stored in Sheet 2 (N1 cell) as MREC1 through VBA code.

When i make another VBA run on Sheet1, E4 cell should increment as written as MREC2 and the same to be stored in Sheet2 (N3 cell) as MREC2 through VBA code.

Again When i make another VBA run on Sheet1, E4 cell should increment as written as MREC3 and the same to be stored in Sheet2 (N5 cell) as MREC3 through VBA code

Again When i make another VBA run on Sheet1, E4 cell should increment as written as MREC4 and the same to be stored in Sheet2 (N7 cell) as MREC2 through VBA code

Note: To have the next incremental value on the E4, you may use the last updated references in Sheet 2 on N column.

Thanks for your usual support.







Thanks
Anwar
 
To make this easier, suggest you upload a sample workbook showing what you have with dummy data. In this manner, we can use your explanation and tie it to actual data and test any VBA. With the current scenario, we are attempting to make this work while trying to understand your explanation. Do not load a picture as we can not manipulate data in a picture. Upload a sample file.
 
Dear Alan,

Sorry for the delayed response. I got into another projects of my company and not focused with requirement. KIndly refer the attached excel sheet where i am expecting the automatic references to appear based on the previous reference in Sheet2.

E4 of sheet1 required the next availabel reference to be place from the Sheet2 N column.
 

Attachments

  • For MREC references.xlsx
    9.6 KB · Views: 4
Hi,​
done with the Macro Recorder :​
Code:
Sub Macro1()
'
' Macro1 Macro
' The Noob Simulator !

'
    With Sheet2.[N1].End(xlDown)
        .AutoFill .Cells.Resize(2), xlFillSeries
        .Cells(2).Copy .Cells(3)
         Sheet1.[E4].Value2 = .Cells(2).Value2
    End With
End Sub
► Do you like it ? ► So thanks to click on bottom right Like ! ◄​
 
Back
Top