Multiple cell address in loop

handric

New Member
Joined
Feb 17, 2023
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have problem with addressing this issue. Is it possible to make this code shorter in any way.
In reality , I am using digital caliper to put some data into excel. Data are the number of the caliper - integer, and measurement - decimal number.
The problem is that caliper is writing this to 2 cells next to each other ( exam. A1 and A2 )and immediately after that putting active cell in next row under B1.
I wrote a code but the number of the rows in this table in worksheet is from 1-50.

Can you please help with this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$E$5" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(-1, 2).Select
End Select
End If
If IsNumeric(Target) And Target.Address = "$G$5" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(-1, 2).Select
End Select
End If
If IsNumeric(Target) And Target.Address = "$I$5" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(0, -4).Select
End Select
End If
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$E$6" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(-1, 2).Select
End Select
End If
If IsNumeric(Target) And Target.Address = "$G$6" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(-1, 2).Select
End Select
End If
If IsNumeric(Target) And Target.Address = "$I$6" Then
Select Case Target.Value
Case Is > 0: ActiveCell.Offset(0, -4).Select
End Select
End If
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
Not sure to understand your objective ... you could test
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim j As Long
j = Target.Column
    Select Case j
        Case 5, 7
            Target.Offset(-1, 2).Select
        Case 9
            Target.Offset(0, -4).Select
    End Select
End Sub
 
Upvote 0
Not 100% understood, but try:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

    'declare condition to exit sub: single cell + value>0
    If .Count > 1 Or Not IsNumeric(Target) Or .Value <= 0 Then Exit Sub
    
    ' If do not want trigger change from row n (i.e, from row 10).
    'If .Count > 1 Or Not IsNumeric(Target) Or .Value <= 0 or .row>10 Then Exit Sub
    
    Select Case .Column
        Case 5, 7 ' column E or G
            .Offset(-1, 2).Select
        Case 9 'column I
            .Offset(, -4).Select
        Case Else
            Exit Sub
    End Select
End With
End Sub
 
Upvote 0
Hi,
Not sure to understand your objective ... you could test
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim j As Long
j = Target.Column
    Select Case j
        Case 5, 7
            Target.Offset(-1, 2).Select
        Case 9
            Target.Offset(0, -4).Select
    End Select
End Sub
Hi,

Maybe to clarify . I need to do measurements in excel table with digital caliper that on each measurement puts inside excel 2 data.
For example : cell A1 - automatically puts number of device - example 2, and cell B1 - decimal reading from measurement.

In the same time , caliper automatically puts cursor in cell A2 - so it is a next row.

I need to do 3 measurements horizontally so that means : A1B1, next data C1D1, next data E1F1 - THEN A2B2, next data C2D2, next data E2F2.

So after first measurement I need to move from A2 cell to C1 Target. Offset(-1, 2).Select , next measurement firstly move from C2 to E1 Target. Offset(-1, 2).Select - then measurement, and finally
after third measurement cursor is in cell E2 - so needs to to move to A2 for next 3 measurements.
Target.Offset(0, -4).Select

I have 50 rows to do measurement.

Maybe this explanation helps.
 
Upvote 0
Hi,

Maybe to clarify . I need to do measurements in excel table with digital caliper that on each measurement puts inside excel 2 data.
For example : cell A1 - automatically puts number of device - example 2, and cell B1 - decimal reading from measurement.

In the same time , caliper automatically puts cursor in cell A2 - so it is a next row.

I need to do 3 measurements horizontally so that means : A1B1, next data C1D1, next data E1F1 - THEN A2B2, next data C2D2, next data E2F2.

So after first measurement I need to move from A2 cell to C1 Target. Offset(-1, 2).Select , next measurement firstly move from C2 to E1 Target. Offset(-1, 2).Select - then measurement, and finally
after third measurement cursor is in cell E2 - so needs to to move to A2 for next 3 measurements.


I have 50 rows to do measurement.

Maybe this explanation helps.
So, with my code in #3:
try to play some twist with this:
column A: case 1
column B: case 2
...
offset(x,y) with x: x rows down(+) or up(-), y: y columns move right (+), left (-)
Its not very hard, I believe.
 
Upvote 0
Not 100% understood, but try:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

    'declare condition to exit sub: single cell + value>0
    If .Count > 1 Or Not IsNumeric(Target) Or .Value <= 0 Then Exit Sub
   
    ' If do not want trigger change from row n (i.e, from row 10).
    'If .Count > 1 Or Not IsNumeric(Target) Or .Value <= 0 or .row>10 Then Exit Sub
   
    Select Case .Column
        Case 5, 7 ' column E or G
            .Offset(-1, 2).Select
        Case 9 'column I
            .Offset(, -4).Select
        Case Else
            Exit Sub
    End Select
End With
End Sub
Thank you very much. This is solution, and I can apply it in some other cases. Most helpful.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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