VBA Code to fill text + series

shaikhstonevilla

New Member
Joined
Dec 5, 2012
Messages
22
Hi,

I have the data as under.

https://www.mrexcel.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOYAAAFZCAYAAACMgVOvAAAY6UlEQVR4nO2dyc7bRrqGdSt9G1wLfS7kACR63fugVw32TsvuK2gCBmIYTpgYhpMYPsxkGEYAeZSnnx7jIVn4Ar6zkCgWi0WyikWp PF7H6CQWOSvgcWHxSpVvVoRAGB2rEK/AQBAG4gJwAyBmADMkJOJ eeff57qqYElqAM /Pbbb3RxcXEsJxPzjz/ ONVTA0tQB3y4e/fuecT8 PHjqZ4aWII64MOdO3fOI aHDx9O9dTAEtQBH27fvn0aMT9//tz49 //z7VU4ORoA748Msvv0wv5ufPn n58 eNx969ezfFUwejSNe0TovQb8ML7nXQpKB0vab1sSSUlaHf03T89NNP04pZSamL ebNG9 nDkeZUZKklCYpcVaTdR20KChVZSxSWicZLcXNoiimE1OVUhfz9evXPk8dlDJLKMnK43 5wrkO2mhilhklCxLz1q1b04ipS6mL fLlS9/3GoiSsuRwAjCvfL51YKIpJveLps7Nmzf9xTRJqYtZlkwPWkNGRVKGsK0DI1ofk/EF08T333/vJ2aXlLqYFxcXU73ns6JfiTlfmbnWgRlDH3PNewxA5caNG PF7JNSF1P/Nw/0kb q8DwBeNZBF5qYrX/z5vr16 PEHJJSPwmePXs29Xs/PcaRvpKyZE0cvzlhWQedLLvFvHbtmruYNlLqYj558uQU7/ kFOnaeNtaZgnL7zQ51kE3y/4e85tvvnET01ZKXczdbneqzwAsQR3w4euvv7YX00VKXcxHjx6d8nMAC1AHfLh69ep5JrE/fPjwVE8NLEEd8OHKlSvnEfP /funempgCeqAD5cvXz6PmPfu3TvVUwNLUAd8 Mvf/9ooq0 fPtHHjx/pw4cP9P79e3r37h29ffuW3rx5Q69fv6ZXr17Ry5cv6eLigl68eEHPnz nZ8 e0dOnT nJkyf0 PFjevToET18 JAePHhA9 /fp3v37tGlS5dQUFAsS0vMU10BLl26dKqnBpZst9vQbwFYotcVxFwwEJMPEFMQEJMPEFMQEJMPEFMQEJMPsxUzj1e0ivMTvRuZQMwp2NEmWtGpT80RYu4nD7uuSTSKudtQtFrRqirRhqrZnLqYedzc7stuE7XEz P9 4g2u8F9OaJX9jkvflPX3/E5z15fMxSzzBJar1PKRiwWbomZx7RaRaQe090mOh7kU580XWJGUUSrVUz5wL4cCSnmKQhTXzMUs2LMKv6mmDnFq/4PZ2wxG3 wP0DH1rZROdXB27/Ofnt1EdD/btW4GESbfL9dea1WRWstfb1pR5soos1Ofd2Y8sZrNi9G/Z9jWvrF7Dtm3ce/bq36P0fr73uPod3xsKsvg0h5rLTernXWf5yGP0NO8SqizSbu/XxhxNxtKBo4AYfE1P 920Tawe4/sTpbzM3u8P7qv23um1Os3JKZX7f6bHUFVX/e3H/oc0yLjZidxyyPmydR4xjtzHIo zdfa39S11LXx9TleNjVl42YLnXWPk79n1X/ 0ro/vM/jJh6JRvoFVGriMMe yuRcqJ0V8aAmNXrHfbvvTVqXGTar9v6W/WzD36OabFrMRs7tE4o48lqvNA2P0ev5BWOx8OuvuzEtK4z462s8h4HP8Pw3SIRazGbt6Om21UfMdWrur7v/gppulUZI2bf55gWPzHV/bV9jfXZ3KclpqkVdDwedvV1LjEPjw1 hjmL6dvHHLwVnkJMOlbIRt3XeEvnI bp pQ6vmIe36/ vidtMe2Ph1V9BWkx z7DrMWsWh37UVnTSaQPkyvPZCemdtVuVbTaie84qfZD9iPFHPwc0 ItZvV I/2i1tHH7OxLd/Ux3Y6HVX3pr121aJ5itvqY2vN1f4aZi0lkuCUc h6zd1S2/2Cbr/6mUVntgFb76e/l LcbikeLOfQ5psVfzLrO2ieWOkrZ/gz9o7LtUU b42FbX833FlPu3WJGtMnV92 jTd/hhOKOQZMyQsPZv7wAWIKAmLyAWIKAmLyAWIKAmLyoSXmKTN/ttstCgrKiIIWc8Fst2gxuaDXFcRcMBCTDxBTEBCTDxBTEBCTDxBTEBCTDxBTEBCTD05i7qNFxv3ceZeY1bzT9txBw3zDzhUJp59nugTqytbmtQ4dYweMa1c70woGtlE9N/dcE/3ngoOYBaXKryi7/qqyWcz9cpk4Ni1E7oiuaE0u7l6hApoYW0zjwt7xtMXUVpI06sxmW37WFThzYfytbJlRkmRku8bEKGY1y9 4hq29fMYU0cA4S rsuInZn73TXBkU0WbXkaXUsZomzql/m/YYxLSlSL1bzHrZjungm1vMqF4Ed9ZFxkvARcze3JqeY29MezCt6aySBjq2Ue9jy2ecmGVGiW8f0xSgZKikzogJi3gS0MRazKHcmkO/0HS3MixmfUF2WawOMQcos4TWDrewFcYEg9bC5faC2XZ0xGEfiOmMm5gD2TuGheb7h9FiToHzqKxrckFFU0xTa6hXsqlC1H4l piuuIlpe9EzpOdZJDagj9mPvZhlRolDn1KnnZLX7tc08zkHWszj/hiVtcW j kiQ1OmdiuoXUAbMvZtG/NeloO9mEWqfIdZlYRsG1BVzO5ofrWizK1q1/dc B5zGL9RWeXY5nHz8Y4sn ZAXUcUZec283etUgTFzB9BYOYPHyCmICAmHyCmICAmHyCmICAmH1piIvMHBWV BS3mgtlu0WJyQa8riLlgICYfIKYgICYfIKYgICYfIKYgICYfIKYgICYfnMQsUmWerOPSL2T hKeubGT zB0HMUvKsnp1SZGukfnDDGOLicyfWTL VnaCaBFk/pwXNzGR ROS0WIW6Zpclmci8yc8LmIi8ycsbmIqazJd10wj8yc81mIi8yc4Xi2mywAQMn/C4yYmMn9C4vF1SUGpQ1IeMn/C4yYmMn9CYi9mmZEyKOuclofMn/DY9zGR RMahxazoHQ9zfeYyPwJg9 oLDJ/zglm/ggCM3/4ADEFATH5ADEFATH5ADEFATH50BITmT8oKPMraDEXzHaLFpMLel1BzAUDMfkAMQUBMfkAMQUBMfkAMQUBMfkAMQUBMfkwUsz9vNnxC6WH8nx8twMTdWXzyPzR50FLWkk0SswyS5wXS5vE7M7z8d0OTBhbzLlm/uw2FCkLEporj5aPu5hlRkmSUeYVLTKU5 O7HZhwE3MumT9D73OZOIpZUpbsf97dL/NnIM/H 9/AhIuY88n8Ob4htJhdFOmakqw8/v 0fcx2rMj47cCEtZizyvwhknjhtRdTi6uctsUkre/hux2YcBNzLpk/h9ZbWDfFUsySskRJL1BK1YIOMSimIb1g/HZgwk3MeWT SJSSKFiuLFrMENj3MeeQ XPorgiUkiiwmN15Pr7bgQm/UdkzZ/7or2F6rQWDmT CwMwfPkBMQUBMPkBMQUBMPkBMQUBMPrTEROYPCsr8ClrMBbPdosXkgl5XEHPBQEw QExBQEw QExBQEw QExBQEw QExBQEw OIlZRYoci8NkWWT hKeubGT zB1nMV0mrqsg8yc8xhYTmT zxElM1xUlKsj8CY bmMj8CYmzmPWtbEoujiLzJzwuYiLzJyyjB3/KLKF1kpFdfgEyf aAtZjI/AmOx6hsnZhnAxIMwuMmJjJ/QuIhZkGpw 0sMn/C4yYmMn9C4iBmQaky8lNmiffXJWgxz4t9HxOZP6FxE1P9DtOhf0mEzJ854Dcqi8yfc4KZP4LAzB8 QExBQEw QExBQEw QExBQEw tMRE5g8KyvwKWswFs92ixeSCXlcQc8FATD5ATEFATD5ATEFATD5ATEFATD5ATEFATD64i1mko bLGsXUlxfpE5Z9t4MGdWVzzPyRs0iayFXMInWevF7REtOwGmS3iTqyYUZsBy2MLeZcM38op1i50OqJCkvHQcySssQtTkSlKebQ2knf7cCEm5jzyvyRlulkL2aZUZJmlCX10q/RP/U tBDXdzsw4iLmvDJ/tJZVAPZiHvqWtYwFpeuR0SJDwUq 24ERazHnkvmjrskUdnvkJqbWvyzSNSWWZkLM8LiJOZ/Mn PzCKpzt1tZg5i2t7PoY4bHTcx5ZP70vs8F4zj4o4hYZpSMDuOqroDdo6q 20Eb z7mDDJ/dhuK9JYXLWYXau6Pff SyPw9pv7bFHp/w3c7aOI3KnvmzJ/W68tpLYkw80cUmPnDB4gpCIjJB4gpCIjJB4gpCIjJh5aYyPxBQZlfQYu5YLZbtJhc0OsKYi4YiMkHiCkIiMkHiCkIiMkHiCkIiMkHiCkIiMkHezHVrB ljFr2ZfX7llPtAyrqyuaR VORx 3lZUtnfIvptbqk6xeh1cem2gdUGFvM2Wb UP1YFFNsvdplGYwW02UtJpGFmETGn/r23wdUuIk5h8yf6t8uy9CWwTgxDYumh7ASs7E b6p9QIWLmHPI/KmfC2JaiekSKVIBMcNjLeYcMn8a8kNMCzELSh36lhUQMzxuYobM/MmNt7QQs48ipbVL5/IA pjhcRMzZOZPz6ixkJQKZzFdB30qBsU8XIF7R1xH7QMq7PuYM8j8MbyGpDp1FNMtS1Zl HtM88nhvw o8BuVPXfmT/u9QMwTgJk/4cHMHz5ATEFATD5ATEFATD5ATEFATD60xETmDwrK/ApazAWz3aLF5IJeVxBzwUBMPkBMQUBMPkBMQUBMPkBMQUBMPkBMQUBMPriJWWaUHPN 3JZ 2WT 7OdCjt0Ghqgrm0Pmj6GuBa3jcxBT 0XpIqW1Q4qBSUzzcR67DQxhbDFnm/kju64dxNQXSLstmIaY4XETM3Tmj y69msxHRZmQszwuIgZPvMHP/WuMjD4s5dzvV473cYS2fQxm6kE7tvAENZiziHzx/S8guraXswyo0RdJD1BrixazPPiJmbIzB9T0ygrx8lazDJLWsl4LjEjEDM8bmKGzPwxvMzEg1Rzx7HF1Ad/7GNGIGZ47PuY4TN/8rj5nvJYThAXkWsfU/v9Et8kdnyPeV78RmXPnPmjv4YgKYkw80cUmPnDB4gpCIjJB4gpCIjJB4gpCIjJh5aYyPxBQZlfQYu5YLZbtJhc0OsKYi4YiMkHiCkIiMkHiCkIiMkHiCkIiMkHiCkIiMkHNzGR cOaurKR TN3HMTUVpMg84cdxhYTmT zxF7MVpRISVmCZV ccBMTmT8h8RATC6W54SImMn/CYi mHi1yWJvpIyYyf86LtZjI/AmO2 CPulA6yShLcSvLCTcxkfkTEo vS5Aryw03MZH5E5LRYhbpuhXO1QfEDI99HxOZP6FxEFPJlF27SUmE7zHngN oLDJ/zglm/ggCM3/4ADEFATH5ADEFATH5ADEFATH50BITmT8oKPMraDEXzHaLFpMLel1BzAUDMfkAMQUBMfkAMQUBMfkAMQUBMfkAMQUBMflgKWZBqXF 7P7x/fzZ/iVgmCsbnrqykfkzdwbFLLOE1uuUstZPve8ntR8XShdpb0AXVpeEx9hiIvNnlljfypa6mGVGSSOMSxNVA2KGx01MZP6EZLyYjhlAEDM8LmIi8ycso8VsidrxWAUyf8JjLSYyf4KDFlMQbmIi8yckfmK2 pjdI7MQMzxuYiLzJyTjxTSNyvYks0PM8Nj3MZH5ExoPMcnpt0zwPWZ4/EZlkflzTjDzRxCY cMHiCkIiMkHiCkIiMkHiCkIiMmHlpjI/EFBmV9Bi7lgtlu0mFzQ6wpiLhiIyQeIKQiIyQeIKQiIyQeIKQiIyQeIKQiIyQdLMbsyf4a21UDM8EBMPgyK2Z35079NB2KGB2LywfpWti doG9bBcQMD8TkA8QUBMTkA8QUBMTkA8QUBMTkA8QUBMTkA8QUBMTkA8QUBMTkA2b CAJi8gFiCgJi8gFiCgJi8gFiCgJi8qElJjJ/UFDmV9BiLpjtFi0mF/S6gpgLBmLyAWIKAmLyAWIKAmLyAWIKAmLyAWIKAmLywVJMc67PPlpkzO9jghBATD4Mitmd61NQmhbN/dJuNSFmeCAmH6xvZQdXkJQZJdY/9Q5CADH5MJ2YRYoWc ZATD5MI2aZUYI 5uyBmHzwFrPMElr33MJWQMzwQEw eIlpk1xQATHDAzH5MF7MMqOkp0 pAzHDAzH5MF7MIlW w6xKQl0NKMQMD8TkA2b CAJi8gFiCgJi8gFiCgJi8gFiCgJi8qElJjJ/UFDmV9BiLpjtFi0mF/S6gpgLBmLyAWIKAmLyAWIKAmLyAWIKAmLyAWIKAmLywVJMc ZPkSrzZAeWfkHM8EBMPgyK2Z35U1KW1atLinSNBIOZAzH5YH0ri2gR/kBMPkwmZpGuqW95JsQMD8Tkg5 YyprMoTXTEDM8EJMPk7aYfQNAEDM8EJMP0/UxqaC0JykPYoYHYvJhvJhlRsqg7GBaHsQMD8Tkg0eLuf9uE99j8gFi8gEzfwQBMfkAMQUBMfkAMQUBMfkAMQUBMfnQEhOZPygo8ytoMRfMdosWkwt6XUHMBQMx QAxBQEx QAxBQEx QAxBQEx QAxBQEx WAppjnzR9 OhdLzBmLyYVDM7swffR IOXcgJh sb2U712OWGSVJRhmiRWYPxOSDp5glZcn 592R TN/ICYfvMQs0rrfCTHnD8Tkw3gxtbhKiDl/ICYfRopZUpYo6QVK6RoggpjhgZh88B/8OYAWc/5ATD5ATEFATD5g5o8gICYfIKYgICYfIKYgICYfIKYgICYP/vL3v7bK6u7du3Tnzh26ffs2/frrr/Tzzz/Tjz/ SEVR0K1bt jmzZv0ww8/0HfffUc3btyg69ev07Vr1 jbb7 lPM/pq6 oqtXr9KVK1fo8uXL9OWXXx6fPHRuCgoKh2IU8 LigqYu1ZODsGy3aDE5YPIFYi4YiMkDiCkMiMkDiCkMiMkDiCkMiMkDezG/ Kw8xf03 PjP9O//qmNHv3z3/R/EHO2QEweWIipyzeFmDnFqxWtjCWm3PND7TYRrWLfZ1kmtZinqwMcf3/sxfzPF/S3LjE7ZLRqMXcbilYRbXbTfSicGN0YW8yJ6wDH3x HPuYVg5iHx/5zZXwfs/Ok2NEm6riK639z/Lf NyuKpjR AbiJ2VMHdBDwuA3Hf0qmEVMpf/tuGjHzeNW46u42Ea2iDe1a/96fDOoJgCt2Ny5i9tbBbkNRxy0vjr8/k4vZ3D5STOOJklPceGzfR4qiprD7P8eJ0YW1mEN1sNtQtFqR6TDj PvjKaZ5kMjUarqLaRqU0PbLY1oZTg6cGN24iTlQB8o uGOZlhOI b/0r7tTiDk0Mni4em9ic98HJ4YRNzFtR2f3dy/VIcfx98dPzLv/pv/Rb2Vdv8c03jK1 406df/nMODQ0x8FNfZ9zOE60PdtiInj78W0YvZ8beI/KruqKzuPLUZpMSpowm9UVq8D5XG1hcTx9wZT8oSBmT88gJjCgJg8gJjCgJg8gJjCgJg8MIqJzB8UlLDFmPlzqqsAUvLCs91uafWPVauAeQExhWESE8yT7Ra5smLQxQTzBWIKQhUTzBuIKYhKTDB/RIqprzUMT3O 6anQKxvMF0sxC0q7fi26SOtflE4y6voFzZaY2nxL35Myj1fWk6f7xMxj8zzP066aCCSmtrwrzi23UZ1ggPmwp2FQzDJLaL1OKTP9cG2R9sqo0hCztdA2p/iMKxKGxIyi6MzLyUKIeVhsXomVx8p6S5ttucMKFOCK9a1s xelS8qSlHp RLpBW8y AKi vBl1LWa9rSVbzxV/UMzDSddaTnb8t0GkPFZa7B1toog2OzWNLqa88bnUz189n7q/eSmWyzHRaVS2vkpH/Ux927THIOZpGC9mmVGSZpQl6 OtrP1PvVcnmVnO/syf6uRtnnzNv2m2wPoawWExd62Lh7uY6nuspTKvW2wfj/2tYv0ZxxwTHbWy2 sma9H6tlHvY2Aqxot56FvWMhaUrhMydUOJzIM/VX uHVnRl/nTXDHfeK6ue0FtRb6VmNV hxN0jJjNBly7FW60SqYWSc/YcT8mOv1i1p 9b5vyiSDmCfETU tfFmnHABH1j8ruW4dm0FN33oydmM14xVVv66M/T32y1X2tMGIeHht5THTQYvLB71bWIGbX7Wz/1yXKiTmYN2MhpjHlYIyY9XNtgreY7sdEB31MPngO/igilhkl6 7BIFXM3SYynPzV1X owt3F3N8yjxRTHXRR/qbxHFWL5ilmq4 pPZ/rMdExjcrW3XL1/fRtax4XiHkaPMQkqr7f3A/ dPcviboGf2xHIFetgY6hW9m6/7qiaLOheLSYVIvXbCKbI67eLWZEm1y9ZTWL4HJMdPq/x yLsGxn uq31RB0WkTO/JEKZv7wAWIKAmLyAWIKAmLyAWIKAmLyoSXmp0 f6OPHj/Thwwd6//49vXv3jt6 fUtv3ryh169f06tXr jly5d0cXFBL168oOfPn9OzZ8/o6dOn9OTJE3r8 DE9evSIHj58SA8ePKD79 /TvXv36NKlS8GzVFBQuBa0mAtmu0WLyQW9rv4f93dVWpuXViwAAAAASUVORK5CYII=


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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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


Code:
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

Code:
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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..


Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,017
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top