Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

set value of cell to the right of the last cell in a range

I am trying to set the value in the cell to the right of the last cell in a range but it isn't working. Can someone see what I'm doing wrong?

Set oWorkbook = Workbooks.Open(TextFile)
       
    Set oRange = ActiveWorkbook.Worksheets(1).UsedRange
       
    For i = 1 To oRange.Rows.Count
        Set oCurRow = oRange.Rows(i)
        For Each oCell In oCurRow.Cells
            If oCell.Value = sCusip Then
                oCurRow.Columns(i, oCurRow.Columns.Count).Offset(0, 1).Value = "Y"
            End If
        Next
    Next
0
boukaka
Asked:
boukaka
1 Solution
 
nutschCommented:
Hi,

This line is wrong for two reasons:
in that range, you want to stay on row 1, not row i (because it's the row range, not the full range
use cells instead of columns, columns doesn't have a row parameter (that I know)

Thomas

oCurRow.Columns(i, oCurRow.Columns.Count).Offset(0, 1).Value = "Y"

Try

Set oWorkbook = Workbooks.Open(TextFile)
       
    Set oRange = ActiveWorkbook.Worksheets(1).UsedRange
       

    For i = 1 To oRange.Rows.Count
       Set oCurRow = oRange.Rows(i)
    if application.WorksheetFunction.countif(oRange.rows(i),scusip) then
                oCurRow.Cells(1, oCurRow.Columns.Count).Offset(0, 1).Value = "Y"
            End If
    Next 

Open in new window

0
 
boukakaAuthor Commented:
Thank you!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now