Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column
Procedure populating the wrong Excel column Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Go Back  Xtreme Visual Basic Talk > > > Procedure populating the wrong Excel column


Reply
 
Thread Tools Display Modes
  #1  
Old 09-13-2013, 10:15 PM
jmazorra jmazorra is offline
Newcomer
 
Join Date: Nov 2012
Posts: 5
Default Procedure populating the wrong Excel column


Hello:

I am having an issue with my code that is just impossible for me to figure out. I have gone over this code several times and cannot find anything wrong with it, but yet the result keeps coming back wrong.

Basically, my code looks down the range of column F and if the value on the cell is <0 then places a N on the cell on column E. In other words if F2 >0 then E2 = N and so on. I got the N/Y part to work. The issue is than instead of populating on column E as I have written is populating in column J. Nowhere in my code do I use column J or any other way to shift the values (at least not that I can see).

I have even tried on a brand new workbook, same results. If I change from column E to Column B, then my results go to column G. So something is shifting my values, just can't figure out what.

Here is my code:
Code:
Option Explicit On

Imports Microsoft.Office.Interop.Excel

Module ImportModule

    Dim xlWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
    Dim xlWSPosition As Excel.Worksheet = CType(CType(xlWB.Sheets("position"), Excel.Worksheet), Excel.Worksheet)

    Public Function GetLastRow(ByVal rngToCheck As Excel.Range) As Long

        Dim rngLast As Excel.Range

        rngLast = rngToCheck.Find(What:="*", SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=Excel.XlSearchDirection.xlPrevious)

        If rngLast Is Nothing Then
            GetLastRow = rngToCheck.Rows.Count

        Else

            GetLastRow = rngLast.Rows.Count

        End If

    End Function

    Sub renameColumns()

        'This is Step 4 when the frmDataImportSplash is activated.

        'This procedure is key for the process. This procedure inserts a column in E.
        'and names it Exemption. It then finds all empty cells in F and places a $ on the cell.
        'This is done to determine XM from NE positions


        With xlWSPosition

            .Application.ScreenUpdating = False

            .Columns("E").Insert(Excel.XlDirection.xlDown)
            .Range("E1").Value = "Exemption"
            .Cells.EntireColumn.AutoFit()

            Dim colValue As Excel.Range = Nothing
            Dim lngLr As Long
            lngLr = GetLastRow(.Cells)

            .Application.ScreenUpdating = True

            For Each colValue In .Range("F2:F" & .Range("F" & .Rows.Count).End(Excel.XlDirection.xlUp).Row)

                If colValue.Value > 0 Then

                    colValue.Cells.Range("E1:E" & lngLr).Value = "N"

                Else

                    colValue.Cells.Range("E1:E" & lngLr).Value = "Y"


                End If

            Next

        End With

    End Sub
End Module
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
 
Procedure populating the wrong Excel column
Procedure populating the wrong Excel column
 
-->