VBA Code to fill text + series

[shaikhstonevilla]

Hi,

I have the data as under.

I will enter some text in B15 cell and i will enter serial number in B16 cell manually.

So what i want is when i run a macro, it will automatically create a invoice number as per B column.

Please help me with macro.


Thank you,

Shaikhstonevilla

 

[silentwolf]

Re: VBA Code Help to fill text + series

Hi,
with everything in VBA many different ways to achive things.
Firstly you could use a formula in B17 =Contatenate(B15," ",B16)
So your chosen invoice Nr: is in "B17" or where ever you like to have it.

Then you can refer to that range via code. But I would use Properties to do so .. Property Let and Get Statements for this.

in the module put following

Option Explicit

Private m_Invoice as variant

Public Property Let Invoice(varInvoice as Variant)
m_Invoice=varInvoice

End Property


Public Property Get Invoice()as Variant
Invoice=m_Invoice
End Property

 

[silentwolf]

Re: VBA Code Help to fill text + series

Sorry for this message I had to leave in a hurry..

I'll post it again clearer..

Private m_Invoice As Variant
'

Public Property Let Invoice(varInvoice As Variant)
    m_Invoice = varInvoice
End Property
Public Property Get Invoice() As Variant
    Invoice = m_Invoice
End Property

Sub TestMyInvoice()
    Dim wksThis As Worksheet
    
    Set wksThis = Worksheets("Tabelle2")        'Change to suit
    
    Invoice = wksThis.Range("B17")              'Here Invoice Property Let
    
    
    'If you like to get your Invoice Nr
    
    wksThis.Range("A1").Value = Invoice         'Here the Invoice Property Get...
    
   
    Debug.Print Invoice
    
End Sub

HTH