Defining an Activecell

Each month I add new data to an existing sheet. I then want to run a macro to 
insert a new column and a formula. Trouble is this data increases rows each 
month and I need to be able to identify the final row each time so I can copy 
the formula down. ie in Jan the data finished at row 100. In Feb it finishes 
at row 200. I need the macro to be able to know to copy the formula down to 
row 200 in Feb for example.
0
Ant (53)
10/3/2005 1:55:31 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
512 Views

Similar Articles

[PageSpeed] 13

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
> Each month I add new data to an existing sheet. I then want to run a macro to
> insert a new column and a formula. Trouble is this data increases rows each
> month and I need to be able to identify the final row each time so I can copy
> the formula down. ie in Jan the data finished at row 100. In Feb it finishes
> at row 200. I need the macro to be able to know to copy the formula down to
> row 200 in Feb for example. 


0
rondebruin (3789)
10/3/2005 2:39:06 PM
Thanks Ron. I copied this into VBA however it debugged stating that it 
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that 
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

> Hi Ant
> 
> You can use this function fir finding the last row with data on thye worksheet
> 
> Function LastRow(sh As Worksheet)
>     On Error Resume Next
>     LastRow = sh.Cells.Find(What:="*", _
>                             After:=sh.Range("A1"), _
>                             Lookat:=xlPart, _
>                             LookIn:=xlFormulas, _
>                             SearchOrder:=xlByRows, _
>                             SearchDirection:=xlPrevious, _
>                             MatchCase:=False).Row
>     On Error GoTo 0
> End Function
> 
> Use this in your code then
> 
> Dim Lr As Long
> Lr = LastRow(Sheets("Sheet2")) + 1
> 
> 
> If you can check one column then use this
> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> 
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
> > Each month I add new data to an existing sheet. I then want to run a macro to
> > insert a new column and a formula. Trouble is this data increases rows each
> > month and I need to be able to identify the final row each time so I can copy
> > the formula down. ie in Jan the data finished at row 100. In Feb it finishes
> > at row 200. I need the macro to be able to know to copy the formula down to
> > row 200 in Feb for example. 
> 
> 
> 
0
Ant (53)
10/3/2005 3:01:06 PM
Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" <Ant@discussions.microsoft.com> wrote in message news:4A757972-A86E-40B4-9D69-0DE4585F7FE5@microsoft.com...
> Thanks Ron. I copied this into VBA however it debugged stating that it
> expected an end sub after the Sub Macro()??
>
> Also, could I do something a bit more simple like using xldown then that
> cell = activecell. Then my formula can copy down to range(activecell)?
>
> "Ron de Bruin" wrote:
>
>> Hi Ant
>>
>> You can use this function fir finding the last row with data on thye worksheet
>>
>> Function LastRow(sh As Worksheet)
>>     On Error Resume Next
>>     LastRow = sh.Cells.Find(What:="*", _
>>                             After:=sh.Range("A1"), _
>>                             Lookat:=xlPart, _
>>                             LookIn:=xlFormulas, _
>>                             SearchOrder:=xlByRows, _
>>                             SearchDirection:=xlPrevious, _
>>                             MatchCase:=False).Row
>>     On Error GoTo 0
>> End Function
>>
>> Use this in your code then
>>
>> Dim Lr As Long
>> Lr = LastRow(Sheets("Sheet2")) + 1
>>
>>
>> If you can check one column then use this
>> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
>>
>>
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
>> > Each month I add new data to an existing sheet. I then want to run a macro to
>> > insert a new column and a formula. Trouble is this data increases rows each
>> > month and I need to be able to identify the final row each time so I can copy
>> > the formula down. ie in Jan the data finished at row 100. In Feb it finishes
>> > at row 200. I need the macro to be able to know to copy the formula down to
>> > row 200 in Feb for example.
>>
>>
>> 


0
rondebruin (3789)
10/3/2005 3:19:49 PM
Thanks again Ron. Very useful. In the end I used:

Sub EndCell()
Dim Lr As Long
Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("C2").Select
Selection.Copy
Cells(Lr, "C").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

This copied the formula in C2 from the last row containing data up to C2 as 
hoped.

Thanks again.

"Ron de Bruin" wrote:

> Copy the in a normal module
> Run the sub test then
> 
> Sub test()
> Dim Lr As Long
> Lr = LastRow(Sheets("Sheet2")) + 1
> MsgBox Lr
> End Sub
> 
> Function LastRow(sh As Worksheet)
>     On Error Resume Next
>     LastRow = sh.Cells.Find(What:="*", _
>                             After:=sh.Range("A1"), _
>                             Lookat:=xlPart, _
>                             LookIn:=xlFormulas, _
>                             SearchOrder:=xlByRows, _
>                             SearchDirection:=xlPrevious, _
>                             MatchCase:=False).Row
>     On Error GoTo 0
> End Function
> 
> 
> Or without the function
> 
> Sub test2()
> Dim Lr As Long
> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> MsgBox Lr
> End Sub
> 
> 
> You can use Cells now like this to build a range
> Cells(Lr, "B") for example
> 
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Ant" <Ant@discussions.microsoft.com> wrote in message news:4A757972-A86E-40B4-9D69-0DE4585F7FE5@microsoft.com...
> > Thanks Ron. I copied this into VBA however it debugged stating that it
> > expected an end sub after the Sub Macro()??
> >
> > Also, could I do something a bit more simple like using xldown then that
> > cell = activecell. Then my formula can copy down to range(activecell)?
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Ant
> >>
> >> You can use this function fir finding the last row with data on thye worksheet
> >>
> >> Function LastRow(sh As Worksheet)
> >>     On Error Resume Next
> >>     LastRow = sh.Cells.Find(What:="*", _
> >>                             After:=sh.Range("A1"), _
> >>                             Lookat:=xlPart, _
> >>                             LookIn:=xlFormulas, _
> >>                             SearchOrder:=xlByRows, _
> >>                             SearchDirection:=xlPrevious, _
> >>                             MatchCase:=False).Row
> >>     On Error GoTo 0
> >> End Function
> >>
> >> Use this in your code then
> >>
> >> Dim Lr As Long
> >> Lr = LastRow(Sheets("Sheet2")) + 1
> >>
> >>
> >> If you can check one column then use this
> >> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >>
> >>
> >>
> >> -- 
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
> >> > Each month I add new data to an existing sheet. I then want to run a macro to
> >> > insert a new column and a formula. Trouble is this data increases rows each
> >> > month and I need to be able to identify the final row each time so I can copy
> >> > the formula down. ie in Jan the data finished at row 100. In Feb it finishes
> >> > at row 200. I need the macro to be able to know to copy the formula down to
> >> > row 200 in Feb for example.
> >>
> >>
> >> 
> 
> 
> 
0
Ant (53)
10/3/2005 3:59:03 PM
Another way without selecting

Sub test()
   Dim LastRow As Long
   With Sheets("SAP (2)")
       LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
       .Range("C2").AutoFill Destination:=.Range("C2:C" & LastRow) _
       , Type:=xlFillDefault
   End With
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" <Ant@discussions.microsoft.com> wrote in message news:1EDDD82B-2C4F-460F-B0A3-EAF69E13DAA2@microsoft.com...
> Thanks again Ron. Very useful. In the end I used:
>
> Sub EndCell()
> Dim Lr As Long
> Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
> Range("C2").Select
> Selection.Copy
> Cells(Lr, "C").Select
> Range(Selection, Selection.End(xlUp)).Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
>
> End Sub
>
> This copied the formula in C2 from the last row containing data up to C2 as
> hoped.
>
> Thanks again.
>
> "Ron de Bruin" wrote:
>
>> Copy the in a normal module
>> Run the sub test then
>>
>> Sub test()
>> Dim Lr As Long
>> Lr = LastRow(Sheets("Sheet2")) + 1
>> MsgBox Lr
>> End Sub
>>
>> Function LastRow(sh As Worksheet)
>>     On Error Resume Next
>>     LastRow = sh.Cells.Find(What:="*", _
>>                             After:=sh.Range("A1"), _
>>                             Lookat:=xlPart, _
>>                             LookIn:=xlFormulas, _
>>                             SearchOrder:=xlByRows, _
>>                             SearchDirection:=xlPrevious, _
>>                             MatchCase:=False).Row
>>     On Error GoTo 0
>> End Function
>>
>>
>> Or without the function
>>
>> Sub test2()
>> Dim Lr As Long
>> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
>> MsgBox Lr
>> End Sub
>>
>>
>> You can use Cells now like this to build a range
>> Cells(Lr, "B") for example
>>
>>
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Ant" <Ant@discussions.microsoft.com> wrote in message news:4A757972-A86E-40B4-9D69-0DE4585F7FE5@microsoft.com...
>> > Thanks Ron. I copied this into VBA however it debugged stating that it
>> > expected an end sub after the Sub Macro()??
>> >
>> > Also, could I do something a bit more simple like using xldown then that
>> > cell = activecell. Then my formula can copy down to range(activecell)?
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Ant
>> >>
>> >> You can use this function fir finding the last row with data on thye worksheet
>> >>
>> >> Function LastRow(sh As Worksheet)
>> >>     On Error Resume Next
>> >>     LastRow = sh.Cells.Find(What:="*", _
>> >>                             After:=sh.Range("A1"), _
>> >>                             Lookat:=xlPart, _
>> >>                             LookIn:=xlFormulas, _
>> >>                             SearchOrder:=xlByRows, _
>> >>                             SearchDirection:=xlPrevious, _
>> >>                             MatchCase:=False).Row
>> >>     On Error GoTo 0
>> >> End Function
>> >>
>> >> Use this in your code then
>> >>
>> >> Dim Lr As Long
>> >> Lr = LastRow(Sheets("Sheet2")) + 1
>> >>
>> >>
>> >> If you can check one column then use this
>> >> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
>> >>
>> >>
>> >>
>> >> -- 
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >> "Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
>> >> > Each month I add new data to an existing sheet. I then want to run a macro to
>> >> > insert a new column and a formula. Trouble is this data increases rows each
>> >> > month and I need to be able to identify the final row each time so I can copy
>> >> > the formula down. ie in Jan the data finished at row 100. In Feb it finishes
>> >> > at row 200. I need the macro to be able to know to copy the formula down to
>> >> > row 200 in Feb for example.
>> >>
>> >>
>> >>
>>
>>
>> 


0
rondebruin (3789)
10/3/2005 4:12:27 PM
That's even better. Exactly what I was after. Many thanks.

"Ron de Bruin" wrote:

> Another way without selecting
> 
> Sub test()
>    Dim LastRow As Long
>    With Sheets("SAP (2)")
>        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
>        .Range("C2").AutoFill Destination:=.Range("C2:C" & LastRow) _
>        , Type:=xlFillDefault
>    End With
> End Sub
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Ant" <Ant@discussions.microsoft.com> wrote in message news:1EDDD82B-2C4F-460F-B0A3-EAF69E13DAA2@microsoft.com...
> > Thanks again Ron. Very useful. In the end I used:
> >
> > Sub EndCell()
> > Dim Lr As Long
> > Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
> > Range("C2").Select
> > Selection.Copy
> > Cells(Lr, "C").Select
> > Range(Selection, Selection.End(xlUp)).Select
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> >
> > End Sub
> >
> > This copied the formula in C2 from the last row containing data up to C2 as
> > hoped.
> >
> > Thanks again.
> >
> > "Ron de Bruin" wrote:
> >
> >> Copy the in a normal module
> >> Run the sub test then
> >>
> >> Sub test()
> >> Dim Lr As Long
> >> Lr = LastRow(Sheets("Sheet2")) + 1
> >> MsgBox Lr
> >> End Sub
> >>
> >> Function LastRow(sh As Worksheet)
> >>     On Error Resume Next
> >>     LastRow = sh.Cells.Find(What:="*", _
> >>                             After:=sh.Range("A1"), _
> >>                             Lookat:=xlPart, _
> >>                             LookIn:=xlFormulas, _
> >>                             SearchOrder:=xlByRows, _
> >>                             SearchDirection:=xlPrevious, _
> >>                             MatchCase:=False).Row
> >>     On Error GoTo 0
> >> End Function
> >>
> >>
> >> Or without the function
> >>
> >> Sub test2()
> >> Dim Lr As Long
> >> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >> MsgBox Lr
> >> End Sub
> >>
> >>
> >> You can use Cells now like this to build a range
> >> Cells(Lr, "B") for example
> >>
> >>
> >>
> >> -- 
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Ant" <Ant@discussions.microsoft.com> wrote in message news:4A757972-A86E-40B4-9D69-0DE4585F7FE5@microsoft.com...
> >> > Thanks Ron. I copied this into VBA however it debugged stating that it
> >> > expected an end sub after the Sub Macro()??
> >> >
> >> > Also, could I do something a bit more simple like using xldown then that
> >> > cell = activecell. Then my formula can copy down to range(activecell)?
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi Ant
> >> >>
> >> >> You can use this function fir finding the last row with data on thye worksheet
> >> >>
> >> >> Function LastRow(sh As Worksheet)
> >> >>     On Error Resume Next
> >> >>     LastRow = sh.Cells.Find(What:="*", _
> >> >>                             After:=sh.Range("A1"), _
> >> >>                             Lookat:=xlPart, _
> >> >>                             LookIn:=xlFormulas, _
> >> >>                             SearchOrder:=xlByRows, _
> >> >>                             SearchDirection:=xlPrevious, _
> >> >>                             MatchCase:=False).Row
> >> >>     On Error GoTo 0
> >> >> End Function
> >> >>
> >> >> Use this in your code then
> >> >>
> >> >> Dim Lr As Long
> >> >> Lr = LastRow(Sheets("Sheet2")) + 1
> >> >>
> >> >>
> >> >> If you can check one column then use this
> >> >> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >> >>
> >> >>
> >> >>
> >> >> -- 
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >> "Ant" <Ant@discussions.microsoft.com> wrote in message news:2C474B82-913F-40BA-AEA3-32BDA862E8F1@microsoft.com...
> >> >> > Each month I add new data to an existing sheet. I then want to run a macro to
> >> >> > insert a new column and a formula. Trouble is this data increases rows each
> >> >> > month and I need to be able to identify the final row each time so I can copy
> >> >> > the formula down. ie in Jan the data finished at row 100. In Feb it finishes
> >> >> > at row 200. I need the macro to be able to know to copy the formula down to
> >> >> > row 200 in Feb for example.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Ant (53)
10/3/2005 4:32:05 PM
Reply:

Similar Artilces:

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...

"too many fields defined" error
I am trying to add a field to my table and keep getting an error message "too many fields defined" and it will not let me add the new field. Does anyone know what I have done to get this error and how I can fix it so I can add fields to my database? How many fields do you have in your table? The maximum number for a single table is 255. Note: it's rare that you would need more than 30 fields in a table if you've properly normalized your table structure. -- Ken Snell <MS ACCESS MVP> "Rachel" <Rachel@discussions.microsoft.com> wrote in ...

user defined fields 01-12-10
I have created a user defined field "GHINno". Using copied code I can emumate user defined fields and find "GHINno". I have code the Sets folder to the "Folder"- "Set MyContact = MyFolder.Folders("Seven Hills Mens Club")". But I can't figure out how to get to the value(s) in "GHINno", either the first value or loop through all values. Any help with UserProperty or Userproperties or whatever would be appriciated. Joe See http://www.outlookcode.com/article.aspx?ID=38 for info on property syntax. You can use a F...

Can't find source of Application-defined or object-defined error!?
I won't post all of the code here because it's too extensive but here's a watered down version (all variables declarations are not listed): Dim i as integer Dim PortName As String Dim ReviewSht As String Dim ModelSht As String Dim HoldingsSht As String Dim Model As Range PortName = Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 15) ReviewSht = "Price Weight - " & PortName ModelSht = "Model Data - " & PortName HoldingsSht = "Holdings - " & PortName Application.ScreenUpdating = False With Worksheets(ModelSht).Range("A9") Range...

Value of cell Defines Range
Hello, I am trying to set a picture to a range of cells which I can do by setting the picture to a range like =$A$1:$C$5. My problem is that the range can change anytime. It will always start in $A$1 and end in column $C but the row for column $C can vary. I know what row column $C should be but I cannot not change the range automatically but have to do it manually. I can put the value for the row in column $C in a cell like D1 but I cannot think of a way to put that in the range automatically (See Example below). Any ideas? Example Range is =$A$1:$C$?. D1 contains the value of 8. So I want ...

Out of context error in user defined function
Apologies to anyone who has already read this in Functions. I realised after a few daysa of no response that I had posted in the wrong stream. Excel 2007 query Error received in VBA window (Debug) = <Out of Context> Error on Spreadsheet = #VALUE# I have 'formatted' this function in much the same way as my twenty or more other functions in this spreadsheet, yet I end up with Out of Context. I have tried various approaches including an 'On Calculate' called sub-routine, but all to no avail. The code for the function is stored in Module1 of an xlam, w...

need help with seeting an activecell in VBA
this is the code i have so far...... Dim rng As Range Application.DisplayAlerts = False Application.ScreenUpdating = False Set wb = Workbooks.Open("f:\Bidditjobs.xls") Set rng = wb.Worksheets("jobs").Columns(1).find(cobOpenPro.Value) ActiveCell.Select Biddit.txtJobNumber.Value = ActiveCell.Value Biddit.cobRep.Value = ActiveCell.Offset(0, 1).Value Biddit.txtMainPrice.Value = ActiveCell.Offset(0, 2).Value Biddit.txtMainPercent.Value = ActiveCell.Offset(0, 3).Value ..... How could I have the ActiveCell.Select begin in the row it finds the cobOpenPro.value? right not is is on...

importing leads, branches defined but won't show in the conversion table
Hi all, I'm trying to import loads of leads but am having some difficulty getting all the information in CRM 1.2 that I want. We wish to import a branch discription ("industrycode" ? / we use the Dutch version) but if I add values to this record they don't show up when I use the lead-import wizard. I can only select the original values that where there from the get go. Anyone any Ideas?? rgds Patrick you need to publish your changes through deployment manager and then do an IISRESET to restart the web server service "Haas" <p.haasewinkel@ecostar.nl> w...

User-Defined Type Not Defined
Seemingly all of a sudden I get the message User-Defined Type Not Defined every time I enter data into any spreadsheet cell. I do not use VB or macros or anything fancy. I don't remember hitting an odd key or ending up in an odd area of Excel. I tried opening a new spreadsheet and the problem persists. I use Excel 2002. How can I stop this from happening? Thank you. -- lg Do you have any add-ins loaded? If so, it is probably an add-in misbehaving. Go in to the VBA Editor, and for each project listed in the Project Explorer (CTRL+R to view it), go to the Tools menu and choose...

Add custom User Defined Function to Report Writer report
Is it possible to do this? I have created a UDF in SQL2005 that returns the value that I want to bring onto a report in GP10's RW. Thanks Les ok.. I think I have learnt since posting that the UDF is a dexterity UDF, not a SQL UDF that Report Writer can reference. If so, forget it.. I'll use a Crystal Report or SQL RS report. Tx Les "LesWright" wrote: > Is it possible to do this? > > I have created a UDF in SQL2005 that returns the value that I want to bring > onto a report in GP10's RW. > > Thanks > > Les You could use VBA to call y...

Can I create User Defined Functions in Excel
Dear All, Can I create my own customized functions in Excel 2000, which can ask arguments & can be distributed as add-in or as an excel file? Any comments. Murtaza Hi Murtaza, Visit http://www.mvps.org/dmcritchie/excel/getstarted.htm and http://www.mvps.org/dmcritchie/excel/formula.htm#install -- Kind Regards, Niek Otten Microsoft MVP - Excel "Murtaza" <sharemomentswithme@hotmail.com> wrote in message news:OLrA$Xo1DHA.2528@TK2MSFTNGP09.phx.gbl... > Dear All, > > Can I create my own customized functions in Excel 2000, which can ask > arguments & ...

Defining a name
Whats up everyone. Can I have a table array that is dynamic based on a input sheet. For example I have a lookup table that =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) I would like CompanyDataQ3 to change based on the input sheet. On the input sheet I have Quarter so if quarter on the input sheet is Q3 the lookup table will have =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) if the quarter on the input sheet is Q4 than the lookup table will hav =VLOOKUP(Reference!$E6,CompanyDataQ4,Reference!$F6,FALSE) Please let me know if this is possible and how to best a...

Modifying user defined data type
Hi How can I modify a user defined data type which is assigned to several fields across db? Thanks Regards You can't, I'm afraid. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "John" <info@nospam.infovis.co.uk> wrote in message news:#IntyI3xKHA.2644@TK2MSFTNGP04.phx.gbl... > Hi > > How can I modify a user defined data type which is assigned to several > fields across db? > > Thanks > > Regards > > > Basically, what Tibor...

Defining relations in XML Schema
Hi, I have a question related to the XML-Schema definition. Is it posible to define relations between elements? The following is my XML Schema: <xs:element name="root"> <xs:complexType> <xs:sequence> <xs:element name="Country" type="Countries"/> <xs:element name="USZipCode" type="xs:string"/> <xs:element name="Adress" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> <xs:simpleType n...

User defined type not defined
I am trying to create a simple macro to insert some text into an OL 2007 message. When I run the macro, I am getting a compile error "User-defined type not defined." Can someone tell me what commands I need to insert or change? Thank you. This is my code. Sub Greetings() ' ' Greetings Macro ' Macro recorded 9/4/2009 by Bob ' Dim objDoc As Word.Document Dim objSel As Word.Selection On Error Resume Next ' get a Word.Selection from the open Outlook item Set objDoc = Application.ActiveInspector.WordEditor Set objSel = ...

How to define Follow-The-Sun service in CRM 2003
Hi, we provide 24/7 support to our customers and I want to define a Follow-the-sun Service in MS CRM 2003. Has anyone already implemented such a service and if so, how? Maybe there are some WhitePapers I haven't found -> please forward me to the correct website ;-) Thank you, Daniel ...

Defining a range
I want to sort a table that grows with additional data. The top left value never changes and is Range("B6") but the bottom right cell will change as the table grows. How do I go about defining the range? -- Bernie Something like: ======== Dim RNG as Range Set RNG = Range("B6", Range("B6").SpecialCells(xlCellTypeLastCell)) RNG.Sort Key1:=Range("B6"), Order1:=xlDescending ======= Does that help you get started? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, cl...

Define Autoscale parameters in Chart
Current autoscale in graphs uses too wide of a scale range making the lines in a line graph look flat. Want to view more of a close up on the range that is graphed. For example: the autoscale may set the scale at 0 - 100 when only the numbers from 20 to 40 are used. Can the autoscale be set to graph only the range with numbers --- like a close up to see the change more distinctly. I have data automatically populating graph tables and don't want to manually go in and set up each one each time there is new data. Any help would be appreciated, it is very tedious to change a pr...

User defined type not defined?
This is a multi-part message in MIME format. ------=_NextPart_000_0023_01CADCBA.E1E83600 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello All I have recently been having some VBA corruption problems, which I have = been taking steps to sort out - one of which has been to do = Debug>Compile db1 after every amendment I make to any of the code. I = have noticed 3 different outcomes of the Debug>Compile db1: 1. Sometimes I get a message "User defined type not defined" - but the = cursor does not move to a...

How Many User Defined fields in Outlook 2007
I want to move data from another database to Outlook. I currently have 33 user defined fields. How many user defined fields are available in Outlook 2007? Thanks, Will Fleenor Not sure what the max is for UDFs (or even if there is one) but from a practical standpoint, if there is a max - it's far greater than can be reasonably managed for a single Outlook item and greatly exceeds 33. One item everyone should keep in the back of their mind is the total number of fields associated with (actually used in/for) a single item (including both standard and user-defined fields...