Selection Range

[G2K]

Hi All.

i need to check values in the selection and perform some calculation accoringly

Selection can be vertical as -
A1 P
A2 P
A3 P
A4 P
A5 P
A6 P
A7 P
A8 P
A9 P
A10 P

or Horiz. as -

A1 A2 A3 A4 A5 A6 A7
CL CL CL CL CL CL CL

i want to set range accordingly and do the calculation using for loop.

Pls help.....

thanks in advance

 

[MickG]

Hi, Select range Horizontal or Vertical, Run code.
The Msgbox will show the address and value of each cell in range.
Modify code for your purpose.

[COLOR=navy]Sub[/COLOR] MG07Oct19
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Selection
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    MsgBox Dn.Address & " / " & Dn.Value
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[lenze]

You can also just refer to the Selection

Sub Something()
Dim cl as Range
For Each cl in Selection
  'some code
Next cl
End Sub

lenze

 

[G2K]

thanks for your reply.

but i am using spreadsheet contorl here and it generate an error -

'Run Time Error '13'
Type Mismatch

Regards

 

[G2K]

Dim Rng As Range
Dim cl As Range
Call dbConnect
Set Rng = Spreadsheet1.Selection' Generate Error - " Type Mismatch"

For Each cl In Rng
Sql = "Update tblAttendance Set Status = 'CL' where EmpId = " & Spreadsheet1.Cells(Spreadsheet1.ActiveCell.Row, 1).Value & " and WorkingDate = #" & VBA.Format(Spreadsheet1.Cells(1, Spreadsheet1.ActiveCell.Column).Value, "dd - mmm - yyyy") & "#"

Con.BeginTrans
Con.Execute Sql
Con.CommitTrans

Next cl

Set Rng = Spreadsheet1.Selection' Generate Error - " Type Mismatch"
For Each cl In Rng - " Type Mismatch"
where Spreadsheet1 is Spreadsheet Object.

thanks

 

[G2K]

thnaks Mick/Lenze

finally i got the solution -

Dim Rng As Range
Dim i As Integer
Set Rng = Spreadsheet1.Selection
For i = 1 To Rng.Count

Spreadsheet1.Selection(i).Activate
'Code
Next i

thanks a ton for your precious time and suggestion