For loop doesn't complete on the first run

[Varidian]

Hi all I have a bit of code that is basically looks to the cell 2 its right, finds the @ and returns the domain name. however, sometimes when I first run the full macro, it only completes about 30 rows even though there are 900 + rows in it.

at first, I thought it was a time issue so I put a wait in for 5 seconds. It's really bugging me

  For x = 2 To Lr
        SplitPos = InStr(1, Cells(x, 1), "@", vbTextCompare)
        Ws_Clean.Cells(x, 2).value = Mid(Cells(x, 1), SplitPos + 1)
  Next x

 

[jasonb75]

The problem will be the value of lr, not the loop.

 

[Zyndstoff]

We would need to see the code were lr is determined, since when it should be 200+ but ends at 30, there should be the problem.

 

[Varidian]

I thought that, but it works if I restart the module, and when I inspect Lr is the correct value.

 

[Zyndstoff]

How are we to help, if you don't show the code?

As one guess: you have 2 worksheets, but in your code you fail to point to the needed worksheet. So depending on which worksheet is active when activating the macro it might work correctly or not.

  For x = 2 To Lr
        SplitPos = InStr(1, WHICHWORKSHEET.Cells(x, 1), "@", vbTextCompare)
        Ws_Clean.Cells(x, 2).value = Mid(WHICHWORKSHEET.Cells(x, 1), SplitPos + 1)
  Next x

 

[Fluff]

Another possibility is

  For x = 2 To Lr
        SplitPos = InStr(1, Ws_Clean.Cells(x, 1), "@", vbTextCompare)
        Ws_Clean.Cells(x, 2).value = Mid(Ws_Clean.Cells(x, 1), SplitPos + 1)
  Next x

assuming both cells are on the same sheet.

 

[Zyndstoff]

Another possibility is

  For x = 2 To Lr
        SplitPos = InStr(1, Ws_Clean.Cells(x, 1), "@", vbTextCompare)
        Ws_Clean.Cells(x, 2).value = Mid(Ws_Clean.Cells(x, 1), SplitPos + 1)
  Next x

assuming both cells are on the same sheet.

Hmmm... am I wrong or does the code not what was stated in the first post: "...code that is basically looks to the cell 2 its right, finds the @ and returns the domain name..."
This code looks up in the left cell (column 1) and returns to the right cell (column 2). Or am I missing something?

 

[Fluff]

Depends on where the OP is from, some countries have the sheets & cells running right to left.

 

[Zyndstoff]

Depends on where the OP is from, some countries have the sheets & cells running right to left.

You are of course correct. Haven't thought of that.

 

[Marc L]

It's really bugging me

Hi, it seems you forgot the worksheet reference for a couple of cells …​