Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Obtain row number of active cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 34
Default Obtain row number of active cell
Hi i am having a problem with the following section of code within the larger
peice below it.

DBaseRow = ActiveRow
Cells(DBaseRow, 5) = TDMCAD

The whole code is suppose to decect specific changes in the "DesignManager"
Workbook and update the values in the "ProjectsMaster" Workbook but i am
having problems obtaining the row address to use in the cell adresses to
paste the values.

This will no doubt be a simple answer but i am banging my head on the wall
as you read this in frustration. Thanks in advance.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name
Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls",
ProjectsMaster = ActiveWorkbook.Name
Windows(DesignManager).Activate

For i = 1 To 1000
If Cells(i, 12) = True Then
ProjectID = Cells(i, 1).Value
DMCAD = Cells(i, 5).Value
DMType = Cells(i, 6).Value
DMNotes = Cells(i, 10).Value
Windows(ProjectsMaster).Activate
Cells.Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
DBaseRow = ActiveRow
Cells(DBaseRow, 5) = DMCAD
Cells(DBaseRow, 6) = DMType
Cells(DBaseRow, 11) = DMNotes
End If
Next i
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,501
Default Obtain row number of active cell
Maybe

DBaseRow = ActiveCell.Row
Cells(DBaseRow, 5) = TDMCAD

Mike

"Jive" wrote:

Hi i am having a problem with the following section of code within the larger
peice below it.

DBaseRow = ActiveRow
Cells(DBaseRow, 5) = TDMCAD

The whole code is suppose to decect specific changes in the "DesignManager"
Workbook and update the values in the "ProjectsMaster" Workbook but i am
having problems obtaining the row address to use in the cell adresses to
paste the values.

This will no doubt be a simple answer but i am banging my head on the wall
as you read this in frustration. Thanks in advance.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name
Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls",
ProjectsMaster = ActiveWorkbook.Name
Windows(DesignManager).Activate

For i = 1 To 1000
If Cells(i, 12) = True Then
ProjectID = Cells(i, 1).Value
DMCAD = Cells(i, 5).Value
DMType = Cells(i, 6).Value
DMNotes = Cells(i, 10).Value
Windows(ProjectsMaster).Activate
Cells.Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
DBaseRow = ActiveRow
Cells(DBaseRow, 5) = DMCAD
Cells(DBaseRow, 6) = DMType
Cells(DBaseRow, 11) = DMNotes
End If
Next i
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 34
Default Obtain row number of active cell
Thanks for that, i knew it would be simple.

"Mike H" wrote:

Maybe

DBaseRow = ActiveCell.Row
Cells(DBaseRow, 5) = TDMCAD

Mike

"Jive" wrote:

Hi i am having a problem with the following section of code within the larger
peice below it.

DBaseRow = ActiveRow
Cells(DBaseRow, 5) = TDMCAD

The whole code is suppose to decect specific changes in the "DesignManager"
Workbook and update the values in the "ProjectsMaster" Workbook but i am
having problems obtaining the row address to use in the cell adresses to
paste the values.

This will no doubt be a simple answer but i am banging my head on the wall
as you read this in frustration. Thanks in advance.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name
Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls",
ProjectsMaster = ActiveWorkbook.Name
Windows(DesignManager).Activate

For i = 1 To 1000
If Cells(i, 12) = True Then
ProjectID = Cells(i, 1).Value
DMCAD = Cells(i, 5).Value
DMType = Cells(i, 6).Value
DMNotes = Cells(i, 10).Value
Windows(ProjectsMaster).Activate
Cells.Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
DBaseRow = ActiveRow
Cells(DBaseRow, 5) = DMCAD
Cells(DBaseRow, 6) = DMType
Cells(DBaseRow, 11) = DMNotes
End If
Next i
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,501
Default Obtain row number of active cell
your welcome

"Jive" wrote:

Thanks for that, i knew it would be simple.

"Mike H" wrote:

Maybe

DBaseRow = ActiveCell.Row
Cells(DBaseRow, 5) = TDMCAD

Mike

"Jive" wrote:

Hi i am having a problem with the following section of code within the larger
peice below it.

DBaseRow = ActiveRow
Cells(DBaseRow, 5) = TDMCAD

The whole code is suppose to decect specific changes in the "DesignManager"
Workbook and update the values in the "ProjectsMaster" Workbook but i am
having problems obtaining the row address to use in the cell adresses to
paste the values.

This will no doubt be a simple answer but i am banging my head on the wall
as you read this in frustration. Thanks in advance.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name
Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls",
ProjectsMaster = ActiveWorkbook.Name
Windows(DesignManager).Activate

For i = 1 To 1000
If Cells(i, 12) = True Then
ProjectID = Cells(i, 1).Value
DMCAD = Cells(i, 5).Value
DMType = Cells(i, 6).Value
DMNotes = Cells(i, 10).Value
Windows(ProjectsMaster).Activate
Cells.Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
DBaseRow = ActiveRow
Cells(DBaseRow, 5) = DMCAD
Cells(DBaseRow, 6) = DMType
Cells(DBaseRow, 11) = DMNotes
End If
Next i
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 110
Default Obtain row number of active cell
Hello

I would suggest a slight amendment to your code:
'...
Windows(ProjectsMaster).Activate
Dim Rng As Range
With ActiveSheet.Cells
Set Rng = .Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If Not Rng Is Nothing Then
Cells(Rng.Row, 5).Value = DMCAD
Cells(Rng.Row, 6).Value = DMType
Cells(Rng.Row, 11).Value = DMNotes
End If
End With
End If
Next i

HTH
Cordially
Pascal


"Jive" a écrit dans le message de news:
...
Hi i am having a problem with the following section of code within the
larger
peice below it.

DBaseRow = ActiveRow
Cells(DBaseRow, 5) = TDMCAD

The whole code is suppose to decect specific changes in the
"DesignManager"
Workbook and update the values in the "ProjectsMaster" Workbook but i am
having problems obtaining the row address to use in the cell adresses to
paste the values.

This will no doubt be a simple answer but i am banging my head on the wall
as you read this in frustration. Thanks in advance.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name
Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls",
ProjectsMaster = ActiveWorkbook.Name
Windows(DesignManager).Activate

For i = 1 To 1000
If Cells(i, 12) = True Then
ProjectID = Cells(i, 1).Value
DMCAD = Cells(i, 5).Value
DMType = Cells(i, 6).Value
DMNotes = Cells(i, 10).Value
Windows(ProjectsMaster).Activate
Cells.Find(What:=ProjectID, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
DBaseRow = ActiveRow
Cells(DBaseRow, 5) = DMCAD
Cells(DBaseRow, 6) = DMType
Cells(DBaseRow, 11) = DMNotes
End If
Next i


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
referring to formula in a non active cell from active cell nickname Excel Discussion (Misc queries) 1 June 21st 07 12:11 PM
How to obtain the number of filtered records in Autofilter Ramez Excel Worksheet Functions 2 August 24th 06 11:00 PM
Obtain last four characters from a cell. mohd21uk via OfficeKB.com Excel Discussion (Misc queries) 3 May 17th 06 02:13 PM
Insert a number of rows based on a value in a cell on active row iRocco Excel Discussion (Misc queries) 1 August 11th 05 06:18 AM
Insert a number of rows based on a value in a cell on active row iRocco Excel Worksheet Functions 0 August 10th 05 08:46 PM


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

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement