CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

VBA not changig data in a cell

this is going to be something really really silly! i have this code:

Public Sub SortMyData()

Dim i As Integer
Dim N_Values As Integer
Dim columnC As String

N_Values = Cells(Rows.Count, 2).End(xlUp).Row

For i = 6 To N_Values
    Cells(i, 3).NumberFormat = "0"

    If Cells(i, 2).NumberFormat <> "0.0%" Then
        Cells(i, 2).NumberFormat = "0.0%"
        Cells(i, 2).Value = Cells(i, 2).Value / 100

        ElseIf (Cells(i, 3).Value) > 1000000 Then
            columnC = (Cells(i, 3).Value / 1000000) & "Mb"

        ElseIf Cells(i, 3).Value = Null Then
            Cells(i, 3).Value = 0

    Else
        Cells(i, 2).Value = Cells(i, 2).Value
        Cells(i, 3).Value = Cells(i, 3).Value
    End If
Next i

End Sub

the code runs fine but the data on the cells does not get changed accordingly. if use debug.print it does print the results that i am looking for. please help!

here is a sample of the data:

1984000
40000000
230000
230000
230000
1984000
230000
16000000

and this is what the debug.print gives me which is the correct result but not showing on the actual cells (this is just an example and not respective to the data provided):

2.048Mb
1.984Mb
230kb
16Mb
230kb
16Mb 
8Mb
2.007Mb
230kb

i still need to add the IF statement for the kb part but i have got that covered.

Upvotes: 0

Views: 43

Answers (1)

bobajob
bobajob

Reputation: 1192

It's not entirely clear what your logic is meant to do, but it looks like one those ElseIfs should be a separate If block.

columnC doesn't seem to be doing anything.

Is this more what you were looking for?

Public Sub SortMyData()

    Dim i As Integer
    Dim N_Values As Integer

    N_Values = Cells(Rows.Count, 2).End(xlUp).Row

    For i = 6 To N_Values
        Cells(i, 3).NumberFormat = "0"

        If Cells(i, 2).NumberFormat <> "0.0%" Then
            Cells(i, 2).NumberFormat = "0.0%"
            Cells(i, 2).Value = Cells(i, 2).Value / 100
        Else
            Cells(i, 2).Value = Cells(i, 2).Value
        End If

        If (Cells(i, 3).Value) > 1000000 Then
            Cells(i, 3).Value = (Cells(i, 3).Value / 1000000) & "Mb"
        ElseIf Cells(i, 3).Value = Null Then
            Cells(i, 3).Value = 0
        End If
    Next i

End Sub

Upvotes: 3

Related Questions