Select Cell & Pass Data To Another Object
Dec 22, 2006
I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.
Private Sub CommandButton1_Click()
Worksheets("PCIDs"). Range("B2").Select 'my issue is here
Range("B2").Activate 'and here
Do Until IsEmpty(ActiveCell)
Call StopService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Range("B1").Activate
Do Until IsEmpty(ActiveCell)
Call StartService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("ServiceName").Select
End Sub
and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.
Public Function StopService(ServiceName As String) As Boolean
Dim oSysInfo As New ActiveDs.WinNTSystemInfo
Dim oComp As ActiveDs.IADsComputer
Dim oSvcOp As ActiveDs.IADsServiceOperations
Dim sCompName As String
Dim sSvc As String
Dim lRet As Boolean
View 3 Replies
ADVERTISEMENT
Aug 17, 2008
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.
Here is my class (I know I can do better with access modifiers/ properties in a serious version):
'The class is called ConsCell
Public Car As Variant
Public Cdr As Variant
Private Sub Class_Initialize()
End Sub
Here is the code from the module which tries to call it:
Function MakeCell()
Cell = New ConsCell 'Execution always stops here-- but with no error message
Cell.Car = 15
Cell.Cdr = "NIL"
MakeCell = Cell
End Function
Function PrintCell(c As ConsCell)
PrintCell = c.Car
End Function
The code for a cell in the spreadsheet itself is this:
=PrintCell(MakeCell())
But it gives me the #VALUE! error.
View 9 Replies
View Related
Apr 10, 2007
Create Range Object & Pass To A Subroutine
Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub
Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub
View 2 Replies
View Related
May 13, 2013
I have a workbook with three sheets:
-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)
-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).
-a dashboard sheet containing an interactive userform for which to populate the database tables.
In this userform, there are two comboboxes:
-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table
- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"
My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?
The code I am working with is:
[Code] .....
In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value
Which is strange because that is showing the selected city name when I run the cursor over the bug.
View 14 Replies
View Related
Jun 29, 2006
I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.
Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents
I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).
View 9 Replies
View Related
Jul 1, 2014
How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.
View 1 Replies
View Related
Jun 16, 2006
I need to select the last cell on a worksheet - that has actual data entered. Note: The "ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell);Activate" - will select the last cell ALLOCATED from memory - i.e. - the last cell that USED to contain data - pior - to being erased. What I am looking for - is the last CURRENTY OCCUIPIED cell with any real data entered.
View 2 Replies
View Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Dec 29, 2013
I have two userforms (for simplicity we'll call them UserForm1 and UserForm2). When pulling up data on UserForm1 and selecting CommandButton1 is Unloads the current form and passes the ComboBox1 on the form to a public variable called lSection_Choice. When CommandButton1 on UserForm2 is selected, it uses the public variable lSection_Choice to determine the ListObject on a worksheet and then locate the last row in the first column of the listobject and input the value of the TextBox1 on UserForm2. It runs through without any errors but the worksheets do not update? I am guessing it's because the modal UserForm2 is set and not focused on the worksheet.
Here is the code to pass the public variable
Code:
Private Sub AddTeam_Click()
lSection_Choice = Me.ComboBox1.Value
Unload Me
[Code]....
View 7 Replies
View Related
May 24, 2007
I am trying to copy/paste the same data range from many sheets into a summary sheet.
Specifically, I'm attempting to copy (B697:G710) from 100 or so sheets into the summary sheet, one after another
I don't know much about VBA and am using the macro recorder. Unfortunately, when I attempt to paste the data from the next sheet to the summary sheet, it overwrites the info from the prior sheet. I have attempted using the down arrow and Ctrl downarrow but the recorded macro continues to select the same cell in the summary sheet into which it pastes the data.
View 9 Replies
View Related
Jul 7, 2008
I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work.
View 9 Replies
View Related
Jun 27, 2007
My goal, albeit a simple one, is to return and store a single value within a Series. It is the first in the series collection.
Dim MyPoint As Integer
MyPoint = Worksheets("My Worksheet").ChartObjects("My Chart").Chart.SeriesCollection(1).Points.Item(1)
However, after i type the period after "Points", no autocomplete listing appears, and when the expression is evaluated, I get the error 'Runtime Error 438 - Object doesn't support this property or method'. How to accomplish the task of retrieve a single data point in a series and 2) Why the Points and Item methods don't seem to work as described in the help documentation
I have been looking for a printable overview of Excel VBA that covers the fundamentals like program structure and syntax, but have been unable to find anything other than pure examples (like many 3rd party sites) or unprintable references (such as Microsoft's help file). If there is a site that covers the fundamentals of using Excel VBA in some depth
View 7 Replies
View Related
Mar 25, 2014
I'm looking to take some data from a selection of about 5 or 6 cells in a row, and use them for some formulas. I can only get as far as selecting the 5 cells.
If I have a row of 5 cells selected, how to I save each cell as a different variable? Does this make sense?
Cell 1 = number1
Cell 2 = number 2
etc..
I want to select them as a row to keep them all in one group. I don't know if this is correct or not.
View 3 Replies
View Related
Oct 6, 2006
In a particular battery sizing workbook I have 3 data tables on each sheet (Sheet2, Sheet3 and Sheet4) The table range is d22:u45, the same on each of these three sheets. What I would like to do is to when any one of three cells (labled "Sheet2", "Sheet3", or "Sheet4") is activated on Sheet1, transfer the data from that reppective sheet table range d22:u45 to the same range d22:u45 on Sheet1. And let me use that data in the Sheet1 worksheet subroutines.
View 2 Replies
View Related
Oct 1, 2006
If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.
I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.
View 6 Replies
View Related
Oct 30, 2006
I want to use the value of the last cell as a starting value and add a number to it and assign it to the next cell.
i have this
Sub a()
For i = 15 To 100
Cells(i, 2).Value = Cells(i - 1, 2).Value + 6
Next i
End Sub
how do i incorporate this
Sheets("Sheet1").Range("A").End(xlUp).Offset(0,0).value
into the code above so that the value of the last cell can be the starting value.
View 3 Replies
View Related
Apr 28, 2008
I have workbook template that I use to generate reports from a list of depts. This list is contained in a drop down cell that is a named range in a different worksheet. My current process is as follows:
-Select Dept Name from the list
-Click a command button which is assigned to code that calculates and saves to a file
-Repeat for next report until all reports are generated
I would like to automate this process by producing all reports with a single command with the following functionality:
-The Dept Name needs to be populated in the specified cell containing the current drop down because it drive various vlookups and other formulas
-If possible, I would like to retain the drop down functionality as I would like to have the option of running an individual report or running the “batch”.
View 2 Replies
View Related
Oct 17, 2007
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.
So the definition is:
Dim Array(1 To 10) As UserType
and access to elements is
Array(5).Element1
The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.
I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.
View 9 Replies
View Related
Jul 22, 2009
I am doing a simple filter list copy and paste into another worksheet and I have a challenge trying to pass the value in Cell B1 (which contains the city) as a filter parameter in the filtered list.
View 2 Replies
View Related
Dec 31, 2013
I have the below code which creates a new template
Code:
Private Sub NewTemplate_Click()
Dim Tsh As Worksheet
Set Tsh = Sheets("TEMPLATE")
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("Please enter new sheet name:")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub
What I would like to add to the above code is two more inputbox prompts, the first should prompt the user for a Name (text and length) to go into the new sheet cell A6. The the second input box should as for code, (number any length), to go into the new sheet cell b6.
View 3 Replies
View Related
Sep 18, 2009
i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box
i've tried
dim arr as variant
arr = range("A1:A6").value
msgbox arr
but it didn't work.
that's the first step of the homework the second is i have 2 list of array
year:1999 - 2002
profit:10,20,30,40
i have to find the max profit and get the year when it occurs
View 9 Replies
View Related
Aug 15, 2008
Following statement works for me:
bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value
But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:
For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row
But I get errror.
View 5 Replies
View Related
Jun 11, 2007
I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.
View 2 Replies
View Related
Jan 1, 2008
I want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:
For i=2 To 5 Step 1
Call Boxcute(ActiveSheet.Cells(i,2))
Next i
Sub Boxcute (Box As String)
Range(Box).Selection
etc.
End Sub
But Excel gives me a error with Box and Range.
View 2 Replies
View Related
Jun 3, 2008
I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.
Public Sub find_date()
Dim x2 As Long
'I am trying to activate the last opened file by using workbooks.count
Workbooks(Workbooks.Count).Activate
Worksheets("Sheet1").Cells(1, 1).Select
x2 = DateValue(Cells(2, 14).Value)
End Sub
Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.
View 4 Replies
View Related
Jun 3, 2008
Why when I want to use a varaible with a value like that 2.1, 0.9, 3.5 in a code to create a formula gives me an error? How to get it work?
Sub Code1()
Dim k As Double
k = 2.1
Range("h11").Formula = "=" & k
End Sub
Strange, but it works well if k is an integer with no Decimal Fraction, like 1, 2, 5, 11 ..
View 4 Replies
View Related
Jan 18, 2012
How to pass data from userform to excel sheet using file path?
View 4 Replies
View Related
Jan 23, 2009
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data.
However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View 2 Replies
View Related
Oct 14, 2013
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, "A")).Select
View 1 Replies
View Related
Nov 27, 2007
i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.
problem is the row where this data is may change so using
limit = (row, col).end(XlRight).Col
View 9 Replies
View Related