Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-20-2017, 11:53 PM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default Macro to select cell based on formula

Hello all,

I record macro's the lazy way, but recording them. Typically this is fine as all actions are recorded by the mouse controls, but I would like to have the first step in the macro select the last cell containing a value in a row, then selecting a vertical range below that cell.

Any idea how to attack this?

Thanks in advance
Reply With Quote
  #2  
Old 09-21-2017, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,968
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Moved to VBA programming forum
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Reply With Quote
  #3  
Old 09-21-2017, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,968
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

I think you can find this type of code easily on the web
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Reply With Quote
  #4  
Old 09-22-2017, 09:04 AM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Peco, et. al,

Thanks for the help. I've been able to get the cell next to the last cell by using the following VBA:

Code:
Selection.End(xlToRight).Offset(0,1).Select
now that I have that cell selected, I can enter the desired formula. I however want to paste that formula through the remainder of the rows... problem is I don't want to type in a specific range, I want to use the range associated with the selected cell...

any ideas how to use an xlToBottom, but selecting all cells between the two ranges? In example B2:F2
Reply With Quote
  #5  
Old 09-22-2017, 12:13 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 443
NoSparks is on a distinguished road
Default

If you ignore this line in post #4
Quote:
any ideas how to use an xlToBottom, but selecting all cells between the two ranges? In example B2:F2
And assuming the active sheet is the one you want to work on and assuming row 2 is the row you want to find the end of,
I think this answers what you've asked
Code:
Sub Test()

Dim c As Long
    
c = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(2, c + 1), Cells(Rows.Count, c).End(xlUp).Offset(0, 1)).Select

End Sub
Seeing this is a single range in an unknown column, how does example B2:F2 relate ?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto enter data based on formula in another cell? RonLEngle Excel 5 06-30-2017 11:44 AM
Change formula cell range based on cell value Scoth Excel 4 10-25-2012 07:51 AM
Creating formula based on if data is correct in cell MattMurdock Excel 1 08-06-2012 03:11 AM
Macro based on cell value ubns Excel Programming 1 05-07-2012 04:03 AM


All times are GMT -7. The time now is 06:52 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft