VBA Active Cell Debugging Issue

A

ajcarlos18

New Member
Joined
Sep 4, 2017
Messages
15
Hi,

Need your help and assistance on the debugging issue I encountered. I've been using a code that runs smoothly when generating the Macro. But when I run the program again using another data, debug issue occurs. I've run series of data combination with one sheet and some run smoothly, but mostly debug issue occurrence. This is the coding where I encountered the issue:
Rich (BB code): 
    Range("D2").Select
    CountB = 0
    Do While CountB = 0
    If IsError(ActiveCell.Value) = True Then
    ActiveCell.Offset(1, 0).Range("A1").Select  --  Debug mode
    ElseIf ActiveCell.Value = "" Then
    CountB = 1
    ElseIf ActiveCell.Offset(0, -1).Value = 1 Then
    ActiveCell.Offset(1, 0).Select
    Else
    StorePg = ActiveCell.Offset(0, -1).Value
    StoreA = ActiveCell.Offset(0, -3).Value
    StoreB = ActiveCell.Offset(0, -2).Value
    StoreNo = StorePg - 1 '*** newwwwwwwwwwwwwwwwwwwwwwwwwwwww
    '=========
    Do While StorePg > 1
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select
    ActiveCell.Value = StoreA
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = StoreB
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = StoreNo
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=""http://eyeshare/eyeshare1/getimage.asp?imageid=""& RC[-2] &""&docid="" & RC[-3] &""&resize=1&width=1100&rotate=0&page=""&RC[-1]"
    StoreNo = StoreNo - 1 '*** newwwwwwwwwwwwwwwwwwwwwwwwwwwww
    StorePg = StorePg - 1
    'ActiveCell.Offset(1, 0).Range("A1").Select '**** newwwwwwwwwwwwwwwwwwww
    Loop
    '=========
    End If
    Loop
  End If

Hope you can help me with this one?

Thanks and More power,
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
if you select D2 and then offset 1 down 0 across you are in E2 yet in the same line of code you select A1, so can you please explain, in words, the logic of this part, thanks.
 
Upvote 0
Welcome to the forum.

What is the error message and is the worksheet protected?
 
Upvote 0
oldbrewer said:
if you select D2 and then offset 1 down 0 across you are in E2 yet in the same line of code you select A1, so can you please explain, in words, the logic of this part, thanks.
Click to expand...


Hi oldbrewer,

This workbook has multiple sheets in it. Data are extracted and pasted on 4 worksheets and what we try to accomplish is auto calculate numbers using formula. That D2 select (from sheet 9) was also applied to C2 and found no problem with it.
Rich (BB code): 
 Sheets(9).Select
    Cells.Select
    Selection.Clear
    Sheets(13).Select
    Range("F1").Select
    SkipBlanks = 0
    StoreSeq4 = 1
    NoLink = 0
    Do While SkipBlanks = 0
    If IsError(ActiveCell.Value) = True Then
    ActiveCell.Offset(1, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 1
    ElseIf ActiveCell.Value = "" And ActiveCell.Offset(0, -2).Value <> "TOTAL PRE-DELIVERY" Then
    ActiveCell.Offset(1, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 1
    ElseIf ActiveCell.Value = "" And ActiveCell.Offset(0, -2).Value = "TOTAL PRE-DELIVERY" Then
    SkipBlanks = 1
    NoLink = 1
    MsgBox "no link"
    Else
    SkipBlanks = 1
    End If
    Loop
    
    
    
    
    If NoLink = 0 Then
    
    SkipBlanks = 0
    StoreSeq5 = 2
    Do While SkipBlanks = 0
    'On Error Resume Next
    
    
    
    If IsError(ActiveCell.Value) = True And Len(ActiveCell.Offset(0, -2).Text) > 0 Then 'value error, total receipt row
    'ActiveCell.Offset(0, 1).Value = Len(ActiveCell.Offset(0, -2).Text)
    ActiveCell.Offset(3, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 3
    
    ElseIf ActiveCell.Text = "" And IsError(ActiveCell.Offset(-1, 0).Value) = True Then 'next
    'ActiveCell.Offset(0, 1).Value = "BBB"
    ActiveCell.Offset(2, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 2
    
    
    
    
    
    ElseIf Left(ActiveCell.Text, 1) = "D" And ActiveCell.Offset(-1, 0).Value = "" Then 'docid
    StoreDocID = ActiveCell.Value
    Sheets(9).Select
    Range("A" & StoreSeq5).Select
    ActiveCell.Value = StoreDocID
    StoreSeq5 = StoreSeq5 + 1
    Sheets(13).Select
    Range("F" & StoreSeq4).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 1
    ElseIf Left(ActiveCell.Text, 1) = "D" And ActiveCell.Offset(-1, 0).Value <> "" Then 'docid repeat
    ActiveCell.Offset(1, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 1
    'ElseIf IsError(ActiveCell.Value) = True And Len(ActiveCell.Offset(0, -2).Value) <> 0 Then 'value error, total receipt row
    'ActiveCell.Offset(3, 0).Range("A1").Select
    'StoreSeq4 = StoreSeq4 + 3
    'MsgBox ("Error")
    ElseIf ActiveCell.Text = "" And Len(ActiveCell.Offset(-1, 0).Text) > 0 Then 'next
    'ActiveCell.Offset(0, 1).Value = "AAA"
    ActiveCell.Offset(2, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 2
    'ElseIf ActiveCell.Text = "" And IsError(ActiveCell.Offset(-1, 0).Text) = True Then 'next
    'ActiveCell.Offset(0, 1).Value = "BBB"
    'ActiveCell.Offset(2, 0).Range("A1").Select
    'StoreSeq4 = StoreSeq4 + 2
    ElseIf ActiveCell.Text = "" And Len(ActiveCell.Offset(-1, -1).Text) > 0 Then 'next
    'ActiveCell.Offset(0, 1).Value = "CCC"
    ActiveCell.Offset(1, 0).Range("A1").Select
    StoreSeq4 = StoreSeq4 + 1
    Else
    SkipBlanks = 1
    'ActiveCell.Value = "dammmmmmmmmmmmmmmm"
    'ActiveCell.Offset(0, 1).Value = Len(ActiveCell.Offset(0, -2).Text)
    End If
    Loop
    
    
    Sheets(9).Select
    Range("B2").Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-1],ImageID!C1:C4,3,0)"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    
    Range("C2").Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-2],ImageID!C1:C4,4,0)"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, -2).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveSheet.Paste
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=""http://eyeshare/eyeshare1/getimage.asp?imageid=""& RC[-2] &""&docid="" & RC[-3] &""&resize=1&width=1100&rotate=0&page=0"""
    Selection.Copy
    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveSheet.Paste
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("D2").Select
    CountB = 0
    Do While CountB = 0
    If IsError(ActiveCell.Value) = True Then
    ActiveCell.Offset(1, 0).Range("A1").Select
    ElseIf ActiveCell.Value = "" Then
    CountB = 1
    ElseIf ActiveCell.Offset(0, -1).Value = 1 Then
    ActiveCell.Offset(1, 0).Select
    Else
    StorePg = ActiveCell.Offset(0, -1).Value
    StoreA = ActiveCell.Offset(0, -3).Value
    StoreB = ActiveCell.Offset(0, -2).Value
    StoreNo = StorePg - 1 '*** newwwwwwwwwwwwwwwwwwwwwwwwwwwww
    '=========
    Do While StorePg > 1
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select
    ActiveCell.Value = StoreA
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = StoreB
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = StoreNo
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=""http://eyeshare/eyeshare1/getimage.asp?imageid=""& RC[-2] &""&docid="" & RC[-3] &""&resize=1&width=1100&rotate=0&page=""&RC[-1]"
    StoreNo = StoreNo - 1 '*** newwwwwwwwwwwwwwwwwwwwwwwwwwwww
    StorePg = StorePg - 1
    'ActiveCell.Offset(1, 0).Range("A1").Select '**** newwwwwwwwwwwwwwwwwwww
    Loop
    '=========
    End If
    Loop
  End If

Thank you
 
Last edited by a moderator:
Upvote 0
Thanks RoryA.

The error message is ActiveCell.Offset(1, 0).Range("A1").Select, and the worksheet is unprotected.
 
Upvote 0
Which cell is active at the time?
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
542
Micron
M
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
508
Mplz
M
Rhodie72
  • Question Question
How to write a data to a text file (VBA)
2
Replies
17
Views
1K
Rhodie72
Rhodie72
U
  • Question Question
Text Value from Textbox to Integer
Replies
1
Views
333
Kaper
Kaper
N
  • Question Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
471
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top