[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


excel vba after calculation comma is wron

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-10-03
Dear experts,

I made a little bit of vba code to import some data from another excel file. In my code i divide 1 plant into two plants,
Function DividePowerplants()

Dim GSRN As String
Dim GSRN1 As String
Dim GSRN2 As String
Dim Factor1 As Double
Dim Factor2 As Double
Dim Name1 As String
Dim Name2 As String
Dim uRng As Range
Dim lCols As Long

    x = Workbooks(WorkbookName).Sheets("DividedPD's").Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To x

With Workbooks(WorkbookName).Sheets("DividedPD's")

    GSRN = .Range("B" & i).Value
    Name1 = .Range("C" & i).Value
    GSRN1 = .Range("D" & i).Value
    Factor1 = .Range("E" & i).Value / (.Range("E" & i).Value + .Range("H" & i).Value)
    Factor2 = 1 - Factor1
    GSRN2 = .Range("G" & i).Value
    Name2 = .Range("F" & i).Value
End With

    With Workbooks(WorkbookName).Sheets("Expected Production")

        Set rangeFind = .Range("3:3").Find(What:=GSRN, After:=Range("A3"), SearchOrder:=xlByColumns)
    If Not rangeFind Is Nothing Then

            lCols = rangeFind.Column + 1
            .Columns(lCols).EntireColumn.Insert Shift:=xlToLeft

            .Cells(1, lCols) = .Cells(1, rangeFind.Column).Value
            .Cells(2, lCols) = .Cells(2, rangeFind.Column).Value
            .Cells(3, lCols) = GSRN1
            .Cells(4, lCols) = Name1

            For j = 5 To 64
                     .Cells(j, lCols).Value = .Cells(j, rangeFind.Column).Value * Factor1
            Next j
            .Cells(3, rangeFind.Column).Value = GSRN2
            .Cells(4, rangeFind.Column).Value = Name2

            For k = 5 To 64
                .Cells(k, rangeFind.Column).Value = .Cells(k, rangeFind.Column).Value * Factor2

            Next k
End If
End With

Next i

Open in new window

The code works fine but the results in my sheet aren't correct. Because when i have a powerplant A and with the code i divide it into Plant B and Plant C
Plant A has the results: 2177 and plant B has 22
                                      1113                          11
Can anyone tell me how this is possible?

Question by:MarkVrenken
LVL 11

Accepted Solution

thydzik earned 1000 total points
ID: 38431424
can you please attach some sample workbooks that produce the errors.

Author Comment

ID: 38457956
never mind i'm sorry for the late response. Haven't been at work for some time. I fixed it. After trying to make an example file i discovered i had some numbers stored as text what produced the mistake.
thanks for making me do it;)


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question