2 Questions....Moving Active Cell And Counting
Okay, the first question
I have found a cell...now i just need to move 3 cells to the right and get that value.
How do I from a column, count how many of that specific number like
222
222
333
333
444
444
444
so 222 = 2
333 = 2
444 = 3
Any information would be greatly appreciated.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Code For Moving To The First Blank Cell In A Worksheet; Moving Left,right,up Or Down
I have created an application form as a userform in Excel. I need to send the data entered into my text boxes when the program runs to separate excel cells in a worksheet.
My problem is I can use code that will send the textbox entry to a specific cell eg. If my textbox is named Surname I can say
range("a5").value = surname but if I do that when I run the programme to enter the next record for someone else the first record will be overwritten.
I NEED A CODE LINE THAT WILL TAKE ME TO THE FIRST EMPTY CELL AND ALLOW ME TO MOVE DOWN OR TO THE LEFT OR TO THE RIGHT
Activating (or Retrieving) The Value Of A Cell, Based On The Current Active Cell
We will test the patience for newbies!
I want to use the address of the current active cell on Sheet1 to move to the same cell on Sheet2 and then to the same column but an offset of a variable number of rows, and then use the value of that cell.
Eg the active cell is C3, an element of an nxn array on Sheet1, the upper left corner is in A1. I want to retrieve the value at the same relative location to the upper left corner pf one of several nxn arrays on Sheet2, where (if necessary) I can specify an offset after activating sheet2.[ C3 ] to Cx, where x is a calculated value.
Thanks from the newest of newbies!
Moving From Cell To Cell By Pressing Enter
Hi,
I'd like to move from cell to cell (within the same row) by using Enter.
I've added following code in datagrid_Keyup event
Private Sub dgLines_KeyUp(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
dgLines.col = dgLines.col + 1
End If
End Sub
The problem is that the next cell value is getting the same value as previous cell but it hasn't.
Can somebody help?
Cell Location Relative To Active Cell
Hi,
I am new to VB but I really need some help with how to copy the value of the cell that located 5 cells (to the right) from the active cell? This value will be pasted to different workbook.
Thanks
Selecting A Cell Based On Where The Active Cell Is
Firstly. Hello gurus, developers, lurkers and other bobs. It is a pleasure to find this place and meet you all.
Secondly. Here is the problemette that I am looking for assistance on.
I have been given an Excel spreadsheet to maintain and improve. What I am looking for is to be able to copy data from one worksheet to another with the following requirements. The data that I select is in worksheet 1, columns A and C which I then want to copy to worksheet 2. The process will be dependent on where the active cell is in worksheet 1, which could be anywhere within the range D3 to L1000. (I hope this makes sense)
Up until being given this spreadsheet to maintain I have never used any form of VB before. I have tried a multitude of searches here and on a few other sites and the results of those indicted using Range(ActiveCell, ActiveCell.End(xlToLeft)).Select in some way but so far I have been unable to make it function as required so I am open to suggestions.
Regards
Youngnutter
Moving Recordset And Counting
I am using the following in my code:
txtCount = 0
If Trim(txtFields(9).Text) <> "" Then txtCount.Text = txtCount.Text + 1
If Trim(txtFields(28).Text) <> "" Then txtCount.Text = txtCount.Text + 1
This works very well in my program and I use it in the Form Load(), now my problem is that txtFields(9) and txtFields(28) are used in a ADO database with Access.
When I use my movelast, movefirst, moveprevious etc.. txtCount still has the same value in, as it is not moving with the recordset. Example txtField(9) has text in and txtField(28) is empty then txtCount = 1.
Now if I move the recordset and txtField(9) has text and txtField(28) has text then txtCount should =2. But its not
Any help anyone please
How To Match The Value Of Active Cell With Other Value Cell
What is the code to match the value of active cell with other value cell in all workbook
I mean :
Example for one cell
Code:
If ActiveCell.Value = Cells(1, 3).Value Then MsgBox "Yes"
I want to check for all cells in workbook
Code:
If ActiveCell.Value = "any cells.Value in workbook" Then MsgBox "Yes"
Counting While A Msgbox Is Active
I have two counters that runs using a timer. So according to the timer Interval the counter goes up. I print an error message(using MsgBox) when the first counter reaches say 100. But until I close the message the second count won't count. How do I increment the second counter when the message box is stile active?
Counting Cell Contents
Hey I'm new to these forums as with VB, I've had to create a 'application' that works out pupils scores etc and converts them into a score using various 'stars' using vlookup tables, the only problem is, I need to create a graph of how many pupils got 1 Star, 2 Stars, 3 Stars, 4 Stars and 5 Stars, I dont want each pupil to have their own part of the pie chart, the idea is that the user can look at the pie chart and see quickly how many people got 5 stars etc, if anybody's got any ideas it'd be much appriciated.
I tried to write a bit of code to count the number of 5's in the cell range but it doesnt appear to work, the code is...
Code:
Function count5(mycells As Range) As Integer
For i = 1 To 12
If cell.i.Value = 5 Then
numcells = numcells + 1
End If
Next i
count5 = numcells
End Function
Also i have attatched a screenshot of my table to remove possible confusion about what the table im trying to make a piechart from looks like.
http://www.aqze58.dsl.pipex.com/as.JPG
Counting The Number Of Characters In A Cell
I have a column of numbers that should be 7 digits. Patient ID numbers.
However, if the number is 0022222, then it only shows as 22222 for example.
I'd normally go to custom format and type 0000000 so that the number would come through as 7 characters.
Now, I need to import this over to Access, which doesnt carry accross the formatting, and the formatting MUST be done before it's brought over to Access.
I think the best way to do it is to make the formatting in the column text, then manually putting in the zeros to make it a seven digit number.
So basically I need help writing a macro that says:
If the active cell has 5 charcters, then add "00" to the start of it.
If the active cell has 6 characters, then add "0" to the start of it.
etc
See where I'm coming from?
I tried
digits = activecell.characters.count
Range("B2") = digits
just to check if it was working, but it gives me an error. Anyone got any ideas?
I'm stuck
Moving The Active Exe
how do I make it so when the form loads, it relocates the program to another directory? i have no idea how to do this any help is appreciated
Moving Forms In An Active VB App
I am doing a report... and I have more than one form... and I need to put the project (with all the forms) from my computer, to a CD, but have all the forms still work. See, the forms are in different directories, but will be in the same on the CD. Is there ANY way AT ALL to do move the forms, and have them still work? Thanks!
How To Set Active Cell ?
Hi, my friends.
I wish to set r (the found range object of only 1 cell) to be the active cell for the following offset function.
I tried many conbinations but failed. Can anybody give me a clue how to fix it ? Many thanks !
Code:
Function RaceDistanceCoefficient(ByVal lDistance As Long, ByVal lMarginDist As Long, _
sCol As String) As Double
Dim rngSource As Range, oCell As Range, r As Range
Dim dSumSpeed As Double, dRaceSpeed As Double, dMarginRaceSpeed As Double
Dim a As Integer
oWB.Activate
Set rngSource = oWB.Worksheets("Sheet1"). _
Range(sCol & "2", Range(sCol & "65536").End(xlUp)) 'Distance Column
For Each oCell In rngSource
Set r = oExcel.Cells.Find(lngDistance)
Next oCell
If Not r Is Nothing Then '[I wish to set r as the active cell for the folllowing codes : ]
While oExcel.ActiveCell.Offset(a, 0) = oExcel.ActiveCell
dSumSpeed = dSumSpeed + oExcel.ActiveCell.Offset(a, 7).Value
a = a + 1
Wend
dRaceSpeed = dSumSpeed / a
How To You Get The Row/col For The Active Cell ?
Just a quick one.
I'm using .OFFSET in a script which manipulates an Excel spreadsheet (see below).
VB Code:
For i = 1 To 53 If Weeks(i) Then ActiveCell.Offset(0, 2).Select ActiveCell.Offset(-1, 0).Value = "Margin" ActiveCell.Value = "Week " & Format(i, "00") ActiveCell.Offset(-1, 1).Value = "Margin %" ActiveCell.Offset(0, 1).Value = "Week " & Format(i, "00") RangeCell = ActiveCell.Offset(0, 1).Row End If Next i
When I have finished this loop, I want to know the cell address of the final entry.
Can anyone tell me the syntax for this as I can't seem to find it.
Thanks.
Active Cell
how do I change the value of ActiveCell, as in move which cell you can get a valur from.
NICK.
Moving To Exact Cell In Flexgrid
I am using a MSFlexgrid that I am having a text box float around so the grid can be editable. I was wondering if there was any way to go to an exact row,column combination. What I want to see is if there is any way to get around first doing flexgrid1.row = 2 then doing flexgrid1.column = 5. Is there any way to say go to 2,5 without moving to the row then to the column?
Thanks in advance.
Moving Form To Follow Active Window....
I have the follow window part, but the problem is that its not fast enough and it looks like its dragging the form when I move the window. Is it possible to do it so that it seems to be PART of the window when move it?
this is what I have:
VB Code:
Private Sub Timer1_Timer()Dim rec As RECT hwin = FindWindow(vbNullString, GetActiveWindowTitle(True)) If hwin = Me.hwnd Then Exit Sub If hwin <> 0 Then GetWindowRect hwin, rec Me.Left = rec.Right * 15 - Me.Width - 1200 Form1.Top = rec.Top * 15 + 60 End If End Sub
Excel Active Cell
G'day,
This may seem a little simple, but I have no idea!!
I want to store the position of the current active cell so I can come back to it after a loop has finished. How should I go about this?
Finding Out What Cell Is Active
HI
This is just a quick question.
Im using vb to manipulate an excel file.
I Need to find the out what cell the activecell is on.
i know the following piece of code will give you the row its on
i.e answer will be a number
answer = OBJECT.ActiveCell.Row
but is there any way to find out what specific row and column it is
on e.g E7 or B4
not just what row its on
any help would be much appricated
thanx in advance
shane
Color Active Cell
Hi Guys,
How do i actually earlier higlighted cell to its original color?
Lets say now i clicked cells(1,1) , it will automatically change it to blue
Thats working fine..but how do i do the following
When i switch to cells(1,2) , the earlier cells will turn to its original color and now cells(1,2) will be highlighted to blue
I can detect the cell being clicked, and higlight it..but how do i change previous higlighted cell to its original color
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells(Target.Row, Target.Column).Select
ActiveCell.Interior.Color = RGB(20, 20, 20)
End Sub
Trying To Delete A Row That A Active Cell Is In
This is the code i have at the moment but i cant find how to delet the row were my active cell is in
what the program does at the moment is check to see if any of the six numbers in the row are the same and if they are delete the row
the part i am hving the problem with is the
worksheet("sheet1").rows(??????).delete were i have pu the question mark is were i am having the porblem at the moment, normaly you just write a number in the and it will delete that row but i am trying to get it to delete the row it is on if there is a number double up
please help!
this is the code iam working on
Application.Cells.Range("d9").Activate
While ActiveCell.Value <> ""
If ActiveCell.Value = ActiveCell.Offset(0, 1).Value Then
Worksheets("sheet1").Rows(??????).Delete
ActiveCell.Offset(0, 1).Activate
End If
ActiveCell.Offset(1, 0).Activate
Wend
Get Active Cell Name In Excel
there's a part in my code that scans for
the strings in the cells of my worksheet.
this would look like this:
VB Code:
Do '.... blah blah Text1.Text = .Cells(i, j) '.... blah blah Loop
what I want to know:
how can I get the cell count or name or
whatever that is:
e.g. "A13" or "B16"
any ideas?
thx!
Active Cell In Ms Excel
hi,
how can i find out which the active cell in ms excel is.
eg. worksheets("sheet1").cell(...????...)
thanks for your help
Hendrik Maier
hendrik.maier@aolcity.de
Stuttgart, Germany
Rename Active Cell
hey how do you define a name to the active cell on a workbook? i no to normally do it its " ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R20C10""
i would of imagened it would be "ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=activecell"" but that doesnt seem to work
ne help?
Selective Moving Of Data From Active Worksheet To Other Worksheets
I am currently trying to copy/move data from the active worksheet to other worksheets.
My active worksheet contains the data which are input through a form. I would like to keep it that way. In addition, I would like to use the location as the criteria to make a duplicate of the data on the active worksheet and move it to the respective worksheets named by the respective locations.
Is there anyway to do it? I try to use If...then...else statements but seems like something is wrong with the code.
Hope someone could advise me. Thanks.
Paste Special To The Active Cell
Hi!
i need some help
I have 200 columns that in the 3nd row on each of them there is an input box. So, in A3, B3, C3, D3, E3 etc there is an input box. I fill the input box with numbers and a formula doing a calculation. The result of the calculation returns to the column IV starting from the row 4.
I want to copy the column IV and paste special to the column that i last use to activate the input box. (Eg.If i was on D3 and i activate the input box i want to paste special the column IV to the column D starting from the D4)
Thanks in advance
Stathis
VBA, Excel, Charting And The Active Cell
I create a chart with VBA in Excel, then start adding series to it. Mindful that the user might have highlighted something before activating my macro (which has no use for the highlighting) I set the active cell to A1 to stop the chart adding the highlighted range as a series.
But then Excel adds the active cell as a series.
Is there an easier way of stopping this or is it a case of just deleting this extra series?
Active Cell In Spreadsheet Object
Hi, I currently have a "Microsoft Office Spreadsheet 10.0" object from the control toolbox's additional controls. So far I have been able to read and write to this spreadsheet. But now I would like to reference the active cell, could someone tell me if this is possible and if so how it can be done.
So far I have used the code:
Code:
Spreadsheet2.Worksheets("Chase").Range("A2").Activate
ActiveCell.Value = "35"
When I run this code I can see the focus shift to the cell "A2" but it just doesn't change. This code was just for testing purposes and I can change the cell to this value using other methods. Ideally I would like the user to click on a cell in the spreadsheet object then be able to extract the information on the cell which has been clicked. I assume "ActiveCell" is the best way to accomplish this.
Thanks Ryan
Excel Locks While A Cell Is Active
[Using: VB6]
Hi
I want the user to enter in some data. To do this, my program opens Excel, then the user enters lots of data in. When the user clicks a 'Finished' button on my form, the program reads in from Excel what the user has typed in, validates it, and bungs it all into my database.
This is working pretty well. I've now come to the testing stage, and I've realised that if the user clicks 'Finished' while an Excel cell is active (ie the user is editing the contents of the cell), then my program cannot access Excel because Excel is busy waiting for the user to finish editing the cell. How might I trap this error?
Any suggestions would be appreciated,
Regards,
John
Selecting A Cell Thats Not On The Active Workbook
i have the following code:
sourceSpreadsheet.Sheets(1).Range("A1").Select
where sourceSpreadsheet is my workbook object.
if the sourceSpreadsheet is the active workbook, then the code works.
however if another workbook is active i get the following error:
'select method of range class failed'
why is this??
kevin
Retrieve De Active Color Of A Cell
I got cells that had there color chaging automatically using a format condition (cond#1:red if B4<10 cond#2:green if C5>100 for example...). Now, I would like to automatically change the color to blue if the active color is red (condition #1 true in my example).
I can get the ColorIndex of the cell:
-when no condition applied = .Font.ColorIndex
-when condition #1 applied = .FormatConditions(1).Font.ColorIndex
-when condition #2 applied = .FormatConditions(2).Font.ColorIndex
But impossible to retrieve the actual cell color... any idea?
Another way to solve my problem would be to be able to know if each condition is true or false. But I need light on that too!!
Thx in advance,
Chib.
ps: I have a feeling this is not a easy one, but any help will be very appreciated.
Changing Active Cell Without Clicking
This is somewhat related to my first question posted. I have this program that does calculations based on user input in an excel spreadsheet. When the user inputs info in a cell, the code uses the input to query a database....
In order to execute the user has to click a button on the spreadsheet. The problem is that users don't know that they must first click out of the active cell before clicking on the button.
What I would like to do is have the active cell change when the button gets focus, so that the user only has to point to the button before clicking clicking the button, not actually click another cell and then click the button.
I have never written code for any got_focus, mouse_move etc. commands...and I'm actually not sure where to put the code...I'm guessing in the sheet's code
Any help would be great.
Thanks
Selection Of Entire Row O Active Cell
Hi there,
I would like to select the entire row of the activecell in order to colour the row.
I am using the below code but it is not good.
Can someone help?
Thanks in advance.
Alex
Paris
Code:
If cbClient1 = "ADI" Then
Row(ActiveCell).Interior.ColorIndex = 40
ElseIf cbClient1 = "CDC ALT" Then
Row(ActiveCell).Interior.ColorIndex = 35
ElseIf cbClient1 = "BGAM" Then
Row(ActiveCell).Interior.ColorIndex = 36
End If
How Do I Select A Range Relative To The Active Cell?
I'm trying to select a range relative from the current active cell either using variables or constants, I don't want an absolute range because I want to loop the operation I'm trying to do several times in a spreadsheet...how do I do it? Thanks.
Move Active Cell After Column Sort
Hi,
simple as this may seem ... it is getting the better of me and i am having trouble finding anything using search.
here is my code
Code:
Private Sub Worksheet_change(ByVal target As Range)
' Check Location of changes and convert to Caps if needed
Application.EnableEvents = False
Dim aryRange(3) As Range
Set aryRange(1) = Application.Intersect(target, Range("C5:C254")) ' Callsign
Set aryRange(2) = Application.Intersect(target, Range("D5: D254")) ' F400 Number
Set aryRange(3) = Application.Intersect(target, Range("E5:E254")) ' Insurance
For Each rng In aryRange
If Not rng Is Nothing Then
For Each cl In target.Cells
If Not cl.Value = "" Then cl.Value = UCase(cl.Value)
Next cl
End If
Next rng
Erase aryRange
' Sort into F400 Number order
Application.Sheets("November").Unprotect ("*****")
Worksheets("November").Range("B5:G254").Sort Key1:=Worksheets("November").Range("D5"), Order1:=xlAscending
Application.Sheets("November").Protect ("*****")
'
Application.EnableEvents = True
End Sub
when i enter the data in any of the monitored cells and hit return the code sorts the sheet into the order i want (and corrects it to caps) ... this all works fine, except that it leaves the cursor in the old row position, it moves 1 cell to the right as expected, but i need the activecell to move to the row of the last entered data, so i can continue to enter other stuff in the remaining cells.
thanks in advance for any help you could give
gunslinger
Doesn't Active Cell Return A Range?
Sub Detector()
Dim Temp As Range
Dim Val As Integer
Dim EnteredDate As Date
If ActiveCell.Column <> 2 Then
Cells(ActiveCell.Row, ActiveCell.Column - 3).Select
End If
If DateAdd("m", 6, Selection) < Now Then
Temp = ActiveCell
Val = ActiveCell.Row
Range("A1").Select
Selection.Font.ColorIndex = 3
ActiveCell.FormulaR1C1 = Selection + Val + " "
Range(Temp).Select
End If
This code isn't working because I get an error when I try to read in the value of ActiveCell as a range. If I cut the As Range in variable dec the code works fine( until the last, where I need a range value of course). Why does this happen?
Thanks in advance!
Excel: Determining Active Cell Position
I am writing a VB Excel Macro and I am using the following code to determine the position of the active cell.
VB Code:
Dim InitialRow As Integer Dim InitialColumn As String InitialRow = ActiveCell.Row InitialColumn = ActiveCell.Column
The problem is ActiveCell.Column returns an integer (i.e. it returns 3 instead of C). Does anyone know a way to return a C, or to convert the 3 to a C)?
If someone knows a function to return the full cell name (Column & Row instead of Column and Row individually), that would also work.
The reason that this is a problem is because I want to select this cell later using the function Range(<Cell>).Select, where Cell = String & Integer
Workbook SaveAs Cell Value In Active Sheet?
Here we go again....
I am now trying to save a workbook and name the file as per the value in cell B2 on the active sheet in that workbook.
thown this together so far but am stuck to make it complete.
VB Code:
'renames sheet to cell valueApplication.Workbooks("Sheet1").Sheets("Master").SelectActiveSheet.Name = Range("A6").Value'this is where the current sheet is renamed and activeActiveworkbook.Sheets.Range("B2").Selectactiveworkbook.SaveAs "C:inst" & cell.Value & ".xls"'now i need to save the entire workbook as the value in Range("B2") of this sheet.
Not sure if any of this is right or if there is a better way to approach it?
I would like ot difine a macro for the command and then call it where ever as in a module? Is this doable?
Property Of An Active Cell (newbie Prob)
Hi
How do I get a property of an ActiveCell...
I want it to do something like this in vb6:
CODERange("A3").Select
If ActiveCell.StrikeThrogh = True Then
MsgBox "Yes"
End If
Setting Active Cell Under Mouse Pointer
Hello:
I am using VB6 and the FlexGrid control.
I am using the right mouse button to show a popup menu, but when the menu pops up it does not move the active cell to be the one under the mouse as I am using the right button not the left.
How can I find which cell is the one under the mouse pointer - from there I think setting the cell as current will be quite simple.
Thanks
Icarusbop
|
TRACKER
