过程填充错误的列 [英] Procedure populating wrong column

问题描述

您好：



我的代码存在问题，这对我来说是不可能的。我已经多次查看了这段代码并且发现它没有任何问题，但结果却一直出现错误。



基本上，我的代码看不起它的范围列F，如果单元格上的值<0，则在列E上的单元格上放置N.换句话说，如果F2> 0，则E2 = N，依此类推。我让N / Y部分工作了。问题不是在E列上填充，而是在J列中填充。我的代码中没有使用J列或任何其他方式来移动值（至少不是我能看到的）。



我甚至尝试过一个全新的工作簿，结果相同。如果我从E列更改为B列，那么我的结果将转到G列。所以有些东西正在改变我的价值观，只是无法弄清楚是什么。



这里是我的代码：

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:

<pre>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)

    Sub renameColumns()

        With xlWSPosition


            Dim colValue As Excel.Range
            Dim lngLr As Long
            lngLr = .Range("E" & .Rows.Count).End(Excel.XlDirection.xlUp).Row



            For Each colValue In .Range("F2:F" & .Range("F" & .Rows.Count).End(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

推荐答案

我改变了你的代码，它适用于我。



更改：

1.更改lngLr以获取F列中的最后一行，根据您的描述包含数据到检查。

2.更改For循环以检查F1（根据需要更改）从第1行循环到最后一行（lngLr）。

3.使用偏移代替将N或Y放入E列的范围。



注意：根据您的电子表格，您可能需要根据需要更改单元格和/或列，如我看到你在使用F2。我把它改成了F1，让我更容易测试。

I changed your code and it worked for me.

Changes:
1. Changed lngLr to get the last row in column F, which according to your description contains the data to be checked.
2. Changed your For loop to check F1 (change as needed) to loop from 1st row to last row (lngLr).
3. Used offset instead of a range to put N or Y in the E column.

Note: You may have to change cells and or columns to suit your needs depending on you spreadsheet, as I see you were using F2. I changed it to F1 to make it easier for me to test.

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)
 
    Sub renameColumns()
 
        With xlWSPosition
 

            Dim colValue As Excel.Range
            Dim lngLr As Long
            lngLr = .Range("F" & .Rows.Count).End(Excel.XlDirection.xlUp).Row 'Last row in column F
            
 
            For Each colValue In .Range("F1:F" & lngLr)'Change range as needed
 
                If colValue.Value > 0 Then
                    'used offset instead of range,ie (E1:E)
                    .Range(colValue.Address).Offset(0, -1).Value = "N"
 
                Else

                    .Range(colValue.Address).Offset(0, -1).Value = "Y"
 

                End If
                 
            Next
 
        End With
 
    End Sub
End Module

这篇关于过程填充错误的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！