It works for me in Excel 2002...
If four adjoining cells are copied then the paste occurs in four adjoining cells.
ubentook
Last Activity: Oct 19 '13, 12:20 AM
Joined: Dec 30 '07
Location:
-
Replace...
Range("A1").Sel ect
With...
Cells(Rows.Coun t, 1).End (xlup).Offset(1 , 0).SelectLeave a comment:
-
Add an apostrophe ' in front of Sub Macro1()Leave a comment:
-
ubentook replied to VBA (Excel) to select random records from multiple tabs and paste them in another tabin Visual BasicYou can get the bottom row from a particular column on a particular sheet by calling this function...
'--
'--Code:.Function GetTheBottomRow(ByRef WS As Excel.Worksheet, Optional ByRef lngCol As Long) As Long If lngCol = 0 Then lngCol = 1 GetTheBottomRow = WS.Cells(WS.Rows.Count, lngCol).End(xlUp).Row End Function
The Rnd function returns random numbers.
'--
You can add data from a row on...Leave a comment:
-
What line causes the error?
What version of Excel are you using?
'--
In the meantime, replace the xlConstants with their numeric value. Access does not know what they are.Leave a comment:
-
You can trap for the display of the SaveAs dialog box in the ThisWorkbook Before Save event.
You can then display the SaveAs dialog box and include the file name to be used...
'--
'In the ThisWorkbook module...
Private Sub Workbook_Before Save(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo OutOfHere
If SaveAsUI Then
Application.Ena bleEvents = False
Cancel = True
...Leave a comment:
-
Add the items from the Excel list to a Collection.
A Collection object will not accept duplicates.
Then add the contents of the Collection to the listbox.Leave a comment:
-
The following uses the "Switch" function. There are other ways to do this.
'--
Sub TheOneIWant()
ActiveSheet.Ran ge("F1").Formul a = MatchThis(Activ eSheet.Range("E 2").Value)
End Sub
Function MatchThis(ByRef E2Value As String) As String
MatchThis = Switch(E2Value = "2009H1", "=S2", _
E2Value = "2009H2", "=V2", E2Value...Leave a comment:
-
Are you really sure the code you posted worked?
...Code:Function NotFunny(ByRef strText As String) As String On Error GoTo HandleError Dim n As Long Dim lngDivisor As Long Dim strTemp As String strTemp = strText lngDivisor = 2 For n = 1 To Len(strTemp) If n Mod lngDivisor <> 0 Then Mid$(strTemp, n, 1) = UCase(Mid$(strTemp, n, 1)) Else Mid$(strTemp,Leave a comment:
-
Here is one way.
It assumes the sheet is actually named "1" (without the quote marks)
To reference the first sheet in the workbook, you would use: Workbooks("RODo cList").Sheets( 1)
'--
...Code:Sub React() Dim wbSheet As Worksheet On Error Resume Next Set wbSheet = Workbooks("RODocList").Sheets("1") If Err.Number <> 0 Then MsgBox ("WorkbookLeave a comment:
-
Following is a simple example. You should be able to use it to modify your existing code.
"Range" or "Cells" without a workbook/sheet qualifier refers to the active sheet.
Close the workbook(s) After copy/paste.
'--
'Both workbooks must be open.
Sub CopyStuffBetwee nWorkbooks()
Dim wbFirst As Workbook
Dim wb2nd As Workbook
Dim copyFrom As Range
...Leave a comment:
-
It may be as simple as adding "Option Explicit" as the first line of code in the module (without the quote marks).
That line of code forces variable declaration/identifies misspelled variable names...
You are setting "precentCop y" to the selection but are
using "percentCop y" when copying....Leave a comment:
-
What version of Excel is in use?
What happens when you run your code?
Is there a particular code line that fails?
Do you get an error message? If so what is the message?
Are you running the code from Excel or are you automating Excel from VB?
Also, the data type, scope and values of all variables should be shown.Leave a comment:
-
Assuming you are Not using xl2007.
Assuming the two checkboxes are the first ones created.
Assuming you are using checkboxes from the Forms toolbar on a worksheet...
ActiveSheet.Sha pes(2).Visible = _
ActiveSheet.Sha pes(1).ControlF ormat.Value > 0Leave a comment:
-
'--
Try removing the "Return False" code line.
'--Leave a comment:
-
A Long covers the range from...
-2,147,483,648 to 2,147,483,647
The # symbol is the type-declaration character that indicates the number is a Double.
You could declare the number as a String and then convert it to a Double when necessary or simply use two different variables - a String and a Double.Leave a comment:
-
If the value is exactly xxx.5 then rounding occurs to the nearest even number...
1.5 rounds to 2.0
2.5 rounds to 2.0
3.5 rounds to 4.0Leave a comment:
-
You don't need to do the find. Just use replace.
Also, selecting is not necessary...
'--
Sub replaceblanks_R 1()
'Macro will replace blanks with "No Data Available"
Application.Cal culation = xlCalculationMa nual
Application.Scr eenUpdating = False
Application.Dis playAlerts = False
Range("D2:G2800 0").Replace What:="", _
Replacement:="N o Data...Leave a comment:
-
'--
Dim intX As Single
- or even better -
Dim sngX as Single
'--Leave a comment:
-
Your method of declaring variables is working against you.
Variables declared within a sub are only usable within that sub.
Variables declared at the top of the module before all subs are public variables that can be used in all subs.
Using "Option Explicit" as the first line in a module generates a msg when you try to use a variable that hasn't been declared. It is good practice to have that line in every...Leave a comment:
No activity results to display
Leave a comment: