Adding an new cell into VBA invoice

[brightwayhr]

Hi Team,

This is my first post I require an help in adding an cell into VBA invoice with formula.

https://drive.google.com/file/d/0B2XVU6Hf2P1tT2xFdEZVenJuYTg/view?usp=sharing

Above was an image link I want to add an two cell in K 24 & K25 & M24 & M25 and the formula is Total plus M23 14% M24 0.5% and M25 0.5% equal to total Grand Total M26.

optionExplicitSub GetEmployeData()
Dim WI As Worksheet, WR As Worksheet
Dim i AsLong, j AsLong, l AsLong, m AsLong, n AsLong, p AsLong, q AsLong
Dim FRowR As Long, FRowI As Long
Dim INV AsString, TINV AsString
Dim Tax AsDouble, Amount AsDouble, Sum AsDouble
Dim AINW


'Setting Worksheet Variables
Set WI = Sheet1
Set WR = Sheet10


FRowR = WR.Cells(Rows.Count, 16).End(xlUp).Row
p = 1
INV = InputBox("Enter the Invoice No. of the Employee you want to add.", "Invoice No.", "BWC/139")
INV = Trim(Replace(INV, " ", ""))
Application.ScreenUpdating = False


For i = 2 To FRowR
    TINV = WR.Range("P" & i).Text
    TINV = Trim(Replace(TINV, " ", ""))



    If TINV = INV And TINV <> "" Then


    j = 20
    If WI.Range("B" & j) <> "" Then
        p = 20
        WI.Rows("20:20").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            WI.Range("B" & j) = WR.Range("B" & i) 'EMP ID
            WI.Range("C" & j) = WR.Range("C" & i) 'CNDT NAME
            WI.Range("E" & j) = WR.Range("D" & i) 'Telephone NO.
            WI.Range("M" & j) = WR.Range("I" & i) 'Inv Amount
            WI.Range("G" & j) = WR.Range("F" & i) 'Date of Joining
            WI.Range("I" & j) = WR.Range("G" & i) 'Program
            WI.Range("K" & j) = WR.Range("H" & i) 'Category

Else
        p = 20
        WI.Rows("20:20").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            WI.Range("B" & j) = WR.Range("B" & i) 'EMP ID
            WI.Range("C" & j) = WR.Range("C" & i) 'CNDT NAME
            WI.Range("E" & j) = WR.Range("D" & i) 'Telephone NO.
            WI.Range("M" & j) = WR.Range("I" & i) 'Inv Amount
            WI.Range("G" & j) = WR.Range("F" & i) 'Date of Joining
            WI.Range("I" & j) = WR.Range("G" & i) 'Program
            WI.Range("K" & j) = WR.Range("H" & i) 'Category
EndIf

Else: EndIf

Next i


If p = 20 Then
i = 20
Sum = 0
Do While IsFormula(WI.Range("M" & i)) <> True
    Sum = WI.Range("M" & i).Value + Sum
    i = i + 1
Loop


WI.Range("StTax") = Sum * 0.15
WI.Range("Total") = Sum * 0.15 + Sum
AINW = SpellNumber(WI.Range("Total").Value)
AINW = Replace(AINW, "Dollars", "Rupees")
AINW = Replace(AINW, "Cents", "Paise")
AINW = Replace(AINW, "  ", " ")
WI.Range("AmtW") = AINW
WI.Range("K13") = INV

 

[Rhodie72]

Hi Team,

This is my first post I require an help in adding an cell into VBA invoice with formula.

https://drive.google.com/file/d/0B2XVU6Hf2P1tT2xFdEZVenJuYTg/view?usp=sharing

Above was an image link I want to add an two cell in K 24 & K25 & M24 & M25 and the formula is Total plus M23 14% M24 0.5% and M25 0.5% equal to total Grand Total M26.

I think that's a tall order considering. Logic suggests that it may be easier to add 2 new rows between 23 and 24 and then do what you want as the area you suggest using now is already merged into a single large contiguous cell. The addition of the 2 rows would allow you to perform the requested actions and also avoid the formatting acrobatics required to reformat the current print area. A formula cannot add in the extra cells but VBA can add extra rows and then fill in the details you provided.

 

[brightwayhr]

Hi Buddy,

I have inserted cell into 23 and 24 but now the issues is am not able to apply formula into those cell, the grand total has to total plus 15% service tax.

 

[Rhodie72]

If the cells are formatted to text then they won't work as expected so check that out first.