Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 6th 20, 04:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Avoid ,,Select" and ,,Selection" in a few lines of code

Hi everybody , and have a pleasant Sunday

I have this code :

Sub macro55()

Application.ScreenUpdating = True

Dim FromWks1 As Worksheet
Dim DestWks As Worksheet
Dim NextRow As Long
Dim myCell As Range
Dim myRng1 As Range
Set FromWks1 = Workbooks("polo2x.xls").Worksheets("1")
Set DestWks = Workbooks("polo2x.xls").Worksheets("1")
With FromWks1
Set myRng1 = .Range("BA11:BA100")
End With
With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1

Else: myCell.Offset(0, -4).Select
Selection.Cut
Selection.End(xlToLeft).Offset(0, 1).Select
ActiveSheet.Paste
End If
Next myCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
End Sub

Can it be removed the Select and Selection ?, (for speeding up
this code ) .

Thank you very much !

  #2   Report Post  
Old December 6th 20, 06:34 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Avoid ,,Select" and ,,Selection" in a few lines of code

Pe duminicńÉ, 6 decembrie 2020, la 05:44:56 UTC+2, Xxer Xxes a scris:
I repost some lines of code :

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1


is wrong . I need if in myCell value is < 3 ,
then , myCell.Offset(0, -4 ) to increment its own value
with value ,,1" ;
so , if in myCell.Offset(0,-4) the value is 235 , it must became
236 ;

now, i have this line of code, but i doesnt do the task I need :
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell.Offset(0, -4).Value = Value + 1



i cant figure it out .
  #3   Report Post  
Old December 7th 20, 07:29 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Avoid ,,Select" and ,,Selection" in a few lines of code

Any sugestion here, please ? ..

  #4   Report Post  
Old December 7th 20, 07:55 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Avoid ,,Select" and ,,Selection" in a few lines of code

Hi,

Am Mon, 7 Dec 2020 10:29:25 -0800 (PST) schrieb Xxer Xxes:

Any sugestion here, please ? ..


try:

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1
Else
myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
Next myCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old December 7th 20, 10:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Avoid ,,Select" and ,,Selection" in a few lines of code

Thanks a lot for response .

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1
is wrong . I need if in myCell value is < 3 ,
then , myCell.Offset(0, -4 ) to increment its own value
with value ,,1" ;
so , if in myCell.Offset(0,-4) the value is 235 , it must became
236 ;


Well this above part I have fixed it :

If myCell < 3 Then
myCell.Offset(0, -4) = myCell.Offset(0, -4).Value + 1

This line of code make me what I need . The above part was solved , working
with your code.
- - - - -
but here , lines of code below, I still have a trouble , and in this macro is my last task
I have to accomplish :

myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1)


,,Columns.Count " starts to count from last column .
I have 5 more columns after
Column(BA) , ( it means Column(BA) ,, to the right " ) ; I use olso and the ,,myCell.Offset(0, -4)" Column with data , that means Column(AW) ,
and the ,,Cut" value must go to range IN THE LEFT of Column (AW) , when it found data
. This last part , ,,Offset(0, 1)" work very well , but the ,,Count" must begin not from last column from sheet but as i just have described .


  #6   Report Post  
Old December 7th 20, 11:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Avoid ,,Select" and ,,Selection" in a few lines of code


but here , lines of code below, I still have a trouble , and in this macro is my last task


,,Columns.Count " starts to count from last column .
I have 5 more columns after
Column(BA) , ( it means Column(BA) ,, to the right " ) ; I use olso and the ,,myCell.Offset(0, -4)" Column with data , that means Column(AW) ,
and the ,,Cut" value must go to range IN THE LEFT of Column (AW) , when it found data
. This last part , ,,Offset(0, 1)" work very well , but the ,,Count" must begin not from last column from sheet but as i just have described .


Well, I just have a work arround with your code , and this lines do my task correctly :

Else: myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, myCell.Offset(0,4)).End(xlToLeft).End(xlToRight).O ffset(0, 1)

Maybe exist a little more elegant solution , or code line writeing ,
but it is already ok .

I thank you so much for your really great help ,
and helpfull assistence . I wish you a great week .




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Range("Reset Sheet!F13").Select Makes VBA Code Fail ToferKing Excel Programming 3 July 11th 07 04:18 AM
VB Script: Code for "Sheets in selection" [email protected] Excel Discussion (Misc queries) 1 February 9th 07 09:52 PM
macro stops on "selection.autofilter" line of code Rob Excel Programming 5 December 10th 05 03:50 PM
Fix Code: Select Case and "Contains" selection Bettergains Excel Programming 5 April 26th 05 02:22 AM
Using "Cells" to write "Range("A:A,H:H").Select" Trip Ives[_2_] Excel Programming 3 June 5th 04 03:13 PM


All times are GMT +1. The time now is 08:14 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017