OLE EXCELL Getting Cell Values
Hi all,
I m using OLE for the first time. I have pasted an OLE Component onto my form with and integrates it with EXCELL. Now when i double click on that component, a worksheet opens and i can type anything in it. What i want is to get that (CELL) value in a variable on command button click.
ny idea....
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Compare String In FlexGrid Cell To Excell Sheet Cell
I have a string in a cell of a flexgrid that I want to compare to a string in an excel sheet cell
I tried doing this:
MsFlexGrid1.Col=0
MsFlexGrid1.Row=0
If MSFlexGrid1.Text = XLSheet3.Cells(3, 1) Then
'Do the stuff I want to do
End If
But it gives me a runtime error and points to this.
Are they of different types? How can I compare these two strings?
Current Cell In Excell
Hi!
I want to make macro, which will put Date in current selected cell.
How can I do this?
Code:
Me.Cells.Item(Row, Column) = Date
How can I get curret row and column?
Thanx!
Copy Excell (cell) With Vb
in cell a1 number is 4
in cell c1 letter is x
in cell a3 number is 3
in cell c3 letter is y
how can i copy 4 times(ref. a1) letter (x) from c1 to colomn f1 to f4 skip line 2 because no data and do the same with line3, 3 times letter y to colomn f5 to f8
thanks
The Color Of An Excell Cell
Hi,
How do I change the back color of an Excell cell and the color of the text in an Excell cell?
R@emdonck
MyCode:
Public Sub stored_procedure_b2()
Const xlExcel5 = 39
Dim XLS As Object ' for the application
Dim XLSheet As Object ' for the worksheet
Set XLS = CreateObject("Excel.Application")
' To Open an existing workbook
XLS.Workbooks.Open ("C:ShiftverslagB2 Usage Report1.xls")
' Open the Worksheet named "Input"
Set XLSheet = XLS.Worksheets("Input")
' Set Column Names
XLSheet.Cells(6, 2) = (str_date + (6 / 24)) 'date
'Open the Worksheet named "plan"
Set XLSheet = XLS.Worksheets("plan")
'set new colors
???????????????????????????
'print
XLSheet.PrintOut
' Don't save
XLSheet.Parent.Saved = True
' DO NOT forget to quit the excel app and also to release the objects...
XLSheet.Application.Quit
' Release the Excel object variables
Set XLSheet = Nothing
Set XLS = Nothing
End Sub
Upper Case In Excell Cell
Hi
Can any body please tell me about any formula in excell to Set the value in any cell to upper case by reference.
i want to set the value of my cells to upper case using any formula and i want to just give the reference. e.g =UPPer(a1,a5)
Upper is not working for reference
Thanks
Reading The Excell Cell Color
Hi,
I'm using OLE Automation for reading the contents of an Excel File. I have to read the cell color (either text color, fillcolor). I'm using Worksheet object for reading the contents. How can I do this?
While using Activesheet.Interior.Color, I got an error "Object doesn't set". How to use this?
The code which i'm using to read the content is
sheetobj.rows.cells(i,j)
Thanks in advance
Help On Outputting Text File From Excell Cell
I am trying to output the text in excell cells.
For example how do you output text file
A B
1 2 Hello
2 5 Hi
3 28 Good
How can I output a tex file containing B1 with the
file title of A1. I also want to make separate text
files for each row.
Any advice of Help would be appriciatted
Thanks
Kuni
Fetching Data From A Web-page Into An Excell Cell
How can I fetch data from a certain web-page into an Excel cell?
Let's say the web page looks like this:
...
Manchester United 12 7 3 2 14:5 25
Bolton Wanderers 12 2 6 4 11:16 12
...
How can I fetch for example Manchester United's number of goals (14) into the Excel cell using e.g. VBA?
Any hints? Solutions?
Remove Numeric Values In Cell Containg Text And Place In Another Cell
Hi everyone,
I have some data in Excel thats in quite a mess. I have attached a sample file as it might make more sense to see it. Basically there is a column containg random text followed by a set of numbers (numbers are always at the end of the text). I am trying to cut the numbers (which vary in length and some are separated by spaces, others by fullstops) and place them in an adjacent cell. Is this at all possible? So far I have only managed to work out a formular to place the numbers that follow the last space into the adjacent cell.
Any help would be most appreciated - can this be done?
About Changing Cell Values To Absolute Values..
Hi there. I got a worksheet containing 3 columns of thousand values. In colume 3, cell values were set as "=a value in column a", eg value of range c2 ="=a3". However, after filling in values in column 3, I tried to sort the values in column 3 in alphabetic order, the values in columns 3 were changed to "=REF!". I know it's about values not being set to absolute values. As there are thousands of values in column 3, Can I use VBA code to change them to absolute values once for all (e.g. change "=a3" to "$a$3)?
Cheers.
Comparing Cell Values In Excel To One Of Several Known Values.
Hi Guys
I am trying to create a macro that will go through each cell in a column in Excell to check if that value matches one of 66 known accpeted string values i have. If the value matches, move onto the next line and if the value of the cell does not match one of the 66 known string values I am comparing it to, then I want the whole row to be deleted.
The problem I am having is I dont know what the best way to handle these 66 string values I am using to compare each cell. As its way to big to use them in an IF statement. Can anyone help me out with this please as it would be greatly appreciated as I am new to Excel macros.
Thanks
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Can U Merge Cell Values Into One Cell?
I tried recording a macro to perform this function, unfortunately it gets confused and keeps what I typed in memory and displays only that.
Specifics...
WorkSheet1.(Sheet1) - Contains A1 name; A2 Address; A3 Address2; A4 Contact Name
I need the text on sheet2 to be displayed in verticle format so that when the sheet is printed out this can be displayed properly in the Envelope window...
WorkSheet1.(Sheet2) - I have made the mistake of making one cell to do this job.
I'm wondering if there is a function to MERGE cells from sheet1 to this cell.
ie. Name (Alt+Enter)
Address (Alt+Enter)
Address2 (Alt+Enter)
Contact Name (Alt+Enter)
(Alt+Enter)
(Alt+Enter) When finished the cell displays contents in verticle form.
This is what I am doing at the moment to make it fit into the envelope window correctly while not screwing up the rest of my long endured page.
There is 50 names and addresses to enter. I do not wish to do this manually if I can help it. If not.. woah is me
Cell Values
HI,
I'm stuck on something here. I have a custom made program that places data into an excel file. I want to do a find on a certain text in a column- i know how to do this bit with vb code, however when you do do this it kinda activates the cell that that text is in then if you press the down arrow in excel it will move to the row below it....well my problem is that i want to return the row value and column value that the find feature bought it to, for example i do a find on "99/000" it finds it on row 567 col 1, i want my code to be ActiveSheet.Cells(row,col).Select where as the row and col values are what has been selected.....any idea how to do this?
Thanks
Incrementing Cell Values
I've developed an application, in excel, where the user inputs information into a form and then clicks an OK button to send all the info to certain cells in Excel. The user then has an option to click on a button to do it all again. My question is, how can I increment the value of a variable when the user click on the "Do Another" button. Normally I would put it in a loop and the value would increase when it cycles through the loop, but in this case I only want the value to increase when the user actually clicks the "Do Another" button. The problem is whenever I set the value of the variable at the begining or the Sub another, everytime the user clicks on the "Do Another" button the value is reset back to the original value instead of continually increasing. What's the best way to get around this
Code:
Dim MP, SH, AP As Long
Sub another()
MP = 11
SH = 12
SP = 29
With Worksheets(2)
'Male plug values
.Cells(7, 4).Value = Application.UserName
.Cells(MP, 2).Value = Worksheets(1).Cells(60, 1)
.Cells(MP, 4).Value = Worksheets(1).Cells(58, 6).Value & _
""" DIA. " & Worksheets(1).Cells(31, 4).Value & Space(1) & Worksheets(1).Cells(58, 1).Value
'Spun head values
.Cells(SH, 2).Value = Worksheets(1).Cells(60, 8)
.Cells(SH, 4).Value = Worksheets(1).Cells(58, 13).Value & _
""" DIA. " & Worksheets(1).Cells(31, 4).Value & Space(1) & Worksheets(1).Cells(58, 8).Value
'Spiral pipe values
.Cells(SP, 2).Value = Worksheets(1).Cells(36, 2)
.Cells(SP, 4).Value = Worksheets(1).Cells(27, 6).Value & _
""" DIA. " & Worksheets(1).Cells(31, 4).Value & Space(1) & "SPIRAL PIPE X" & _
Space(1) & Worksheets(1).Cells(27, 8).Value & """ LONG - STANDARD GAUGE"
End With
MP = MP + 2
SH = SH + 2
SP = SP + 1
UserForm2.Show
End Sub
Two Values In Excel Cell
Can you store two values in an excel cell? I don't think comments will work because I don't want the user to know the value that is not being displayed.
How To Separate Values Within The Same Cell
first off thanks for the read and would greatly appreciate any help out there.
so right now, what i'm try to do is separate a city and two-character state (ex. New York NY) from the same field. how do i go about splitting this up into their own (city and state) separate columns? i'm a newb as you can see so again, would greatly appreciate help.
Concatenation Of Cell Values
Hi,
Please help me in this. Here is my scenario
I am having 3 sheets. Each sheet contains some header. But out if these header i need to take values from only three header Namely ID,value/Data and Platform. In ID cell only one value is present. In Value/Data cell containg more than single line. I need to take the first line. In platform cell i may have more than one value in each line of a cell. So my output value is in a different sheet name as Sheet1-Mod in a single cell. Resultant value should come as ID_Platform_value/Data if only one platform value. If 2 values then it will come as ID_Platform1_value/Data and ID_Platform2_value/Data in next cell.Excel sheet row is a dynamic one. How to do it with VBA.
Refer attachment
Thanks in Advance,
Jeya
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Lock Cell Values
Hi,
How do I lock cell values when copying data from one sheet to another so that when the paste takes place the cell values are in tact but they dont have any formulas associated with them? The reason is that I delete some cells on the page where the cells are pasted, and if they still have formulas associated then it all goes wrong.
I'm thinking there must be a little bit of VB code I can put in with my copy/paste code that will carry the cells but not the formulae.
Thanks,
Daryll
Calculating Cell Values Using DDE
Hello again,
if anyone has read my previous thread, you know I'm a totally novice working with excel and VB.
I'm having lots of problems trying to update the values of an Excel who uses the Bloomberg DDE Server to calculate their cell values.
I'm trying, from VB, to open the workbook, recalculate all the values and check when the update has been done, but the "basic" methods (checking the Application.CalculationState value) doesn't work (or I don't know how to make it work).
Now, I'm trying to start the DDE Server from my program and execute, one by one, every formula. But I don't know how to do it. My idea is this:
Code:
Set objExcel = New Excel.Application
objExcel.Volatile
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkBook = objExcel.Workbooks.Open(ruta, 3)
'Init the server
lngChannel = DDEInitiate("Blp", "S")
For Each objWorkSheet In objWorkBook.Worksheets
objWorkSheet.EnableCalculation = False
objWorkSheet.EnableCalculation = True
Next
For Each objWorkSheet In objWorkBook.Worksheets
'loop through rows and columns and send to execute the formula
DDEExecute lngChannel, CStr(objWorkSheet.Cells(x, y).Formula)
Next
objWorkBook.SaveAs FileName:=ruta2, ConflictResolution:=xlLocalSessionChanges
objWorkBook.Close True
Set objWorkBook = Nothing
objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing
DDETerminate (lngChannel)
I don't know if this will do what I want, or maybe I have to use DDERequest to question for the value, take the parameter and save it in the cell.
Any hint?
Combining Cell Values
I was wondering if anybody has a solution to the problem I've run into
I'm currently working in Excel with a large spreadsheet(25,000 entries). Its essentially a list of invoices, The sheet has 2 coumns, customer and amount.
There is perhaps about 100 different types of customer and I was hoping to find a way to combine the orders made by each customer and have one spreadsheet with details of the total orders by each customer.
any ideas?
Cell Values As Variables
I`m hoping someone can help me with this.
Looking to do the following :
User clicks a cell, cell value stored as variable. If user changes cell vale, variable pasted into a cell and the new cell value is pasted next to it.
Can anyone help or point me in the right direction ?
Regards,
Manipulating Cell Values
I'm trying to manipulate a column of cells which have a set of eight numbers which I want to convert to a calendar date. Ex. 20031019. It seems that I need to put /'s in to make it 2003/10/19, in order to make excel recognize it as a date. I tried to use a custom date format, but can't get it to work. Am I doing something wrong? If not, how do I write a macro to automatically add the /'s within the cell to change it to a date? I apologize for my ineptness. Maybe, I'm just having a brain freeze this morning.
Reading The Different Values Of A VB Cell
hello
Is there a way that i can read the different values in an excel cell?
what i mean is say i have a cell B12 and it has three values
white, green, blue
which i have populated using the
data --> validation --> list
can i read those values using the VB program using a variation in the command
Code:
colorName = ptrexcelcolor.Worksheets("colors").Cells(12, 2)
Incrementing Cell Values
Hi All,
I've been trying to increment the value of cells in an OLE Excel spreadsheet. However I seem to run into trouble with my code since it seems to me that, if I assign a variable to a cell's value it is taken as a string. ie:
Stat = MySheet.Cells(Col, Row).Value
This will result in Stat containing a string value when I need it as an integer in order to increment the value. I had hoped I could explicity cast Stat as an int, but I had no luck there either.
Can anyone please help?
Thanks,
Chris S.
Changing Cell Values
Hi there,
I'm a newbie in VBA and I have a question.
How do I change partial cell value? meaning, for example :
change "It is a nice day" to "It was a nice day"
Thankis, Udi
Copy Cell Values And Format
Helo everyone!
I would like to have some support on a situation here...
This is the situation:
I'm currnetly working on a Word Template with some Macros on it.
This template is used by a software to update some information. but the core point is this: the is a source table that is autamatically build when the program updates the information. The number of Columns in this table is always the same, but the row count varies depending on the data coming from the software mentioned. I want to copy this table (values and Format) to ANOTHER EMPTY table (that's already created), this one has no borders, and more columns than the source (the other columns have some data).
the final output that I want is the values and format from the source table, pasted on "part" of another EMPTY table with different dimensions.
Could anyone help for a Macro that does all this?
If I didn't explain the situation better or you have some questions, please feel free to ask.
(no Brainer?) Getting Cell Values From Other Workbooks...
I have a spreadsheet in which one column contains a list of spreadsheet files. Each of those files has identical structure: a workbook called "Estimates" a value in cell F10 that I wish to pull into my spreadsheet.
Is this worthy of a macro or is there a much simpler method?
Thanks!
Sum Cell Values Based On Criteria
Hi,
I have an Excel spreadsheet in the format below. I'd like to write a formula that can calculate the trader commission total, as well as the broker commission within each trader. Essentially, it'll add the commission columns for each value relating to trader1 to give an accumulative. It will also add the commission totals for lloyds & hsbc for trader 1.
Then do this for the rest of the traders.
Any ideas on how to do this much appreciated.
Thanks
broker commission Trader
lloyds 3,432 trader1
lloyds 5,433 trader1
lloyds 21,231 trader1
hsbc 4,204 trader1
hsbc 16,884 trader1
hsbc 43,343 trader1
hsbc 4,563 trader1
hsbc 10,000 trader1
abbey 5,500 trader1
abbey 2,323 trader2
abbey 324,234 trader2
lloyds 5,169 trader2
lloyds 544 trader2
lloyds 5,169 trader2
hsbc 5,169 trader2
hsbc 34,343 trader2
hsbc 5,500 trader2
hsbc 5,169 trader2
hsbc 10,000 trader2
abbey 5,500 trader2
abbey 11,550 trader2
abbey 34,343 trader 3
lloyds 10,000 trader 3
lloyds 10,000 trader 3
lloyds 34,343 trader 3
hsbc 10,000 trader 3
hsbc 34,343 trader 3
northern rock 10,000 trader 3
northern rock 34,343 trader 3
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Comparing Cell Values In Excel
Hi!!!
i have two different Excel workbooks.
i have to compare a cell value from sheet1 of workbook1 with all the cell values ie, column A values in sheet1 of workbook2.
I am not very well versed with VB, so i guess i need someone to tell me how to go about it.
And by the way, if the data matches, then column B of workbook2 needs to be updated.
Can anyone help me, please ...................
Noor.
Adding Cell Values In A Macro
I'm trying to add the values of two cells within a macro and then insert the sum into one of those cells.
Not sure what to use here.
I tired
Code:
Worksheets(strStation).Range(Cell7).Value = (SUM(Cell7:Cell8))
Where Cell7 and Cell8 are variables for the two cell locations I want to add together and then put the result in the Cell7 location. And that just gave me a compile error.
help anyone?
Function Won't Change Cell Values
This is driving me nuts!
As a macro in a module (SUB) this code works fine:
Range("H21:H24").Select
Selection.ClearContents
However in the SAME module it does not work in a Function, example
Public MyFunction(strIn as STRING) strOut as STRING
Range("H21:H24").Select
Selection.ClearContents
MyFunction="Yowsa"
End Function
The above function returns the string fine, but does NOT clear the cells specified, why not?
I should mention that this function is called from my worksheet.
Values In An Array In Msflexgrid Cell
i have a msflexgrid that i can poplulate using a recordset. If i have my values set into a array how can i get the cell to load all of the values in the array?
my code to load the values:
Code:
Do Until rs8.EOF
With MSFlexGrid1
For iRow = 1 To .Rows - 1 'we don't want the header row, just 1 till end of count
If .TextMatrix(iRow, 0) = rs8!patient_id Then
matchFoundp = True
myArrayE(arraycounterp) = rs8!term
.TextMatrix(iRow, 5) = myArrayE(arraycounterp)
arraycounterp = arraycounterp + 1
Else: matchFoundp = False
' Exit For
End If
Next iRow
End With
rs8.MoveNext
is there a way i can do :
Code:
.TextMatrix(iRow, 5) = myArrayE(1) + myArrayE(2)
in a loop so that it loads all the values in the array?
Adding Values In 1 Cell Of MSFlexgrid
Following on from my previous question which was thankfully solved by you guys Its led me on to one final query!
I now retrieve figures into a cell of MSFlexgrid, sometimes these cells contain 2,3 or 4 lines in each cell.
Is there a way to add the figures from a single cell in msflexgrid which has figures seperated by a line drop.
For example Cellmatrix 1,1 in msflexgrid contains the following information with multiline :-
12
300
24
I need to write something which detects each of those figures in that single cell and adds each line together, as currently my column calculation function thinks this cell is valued at 1,230,024 when it should be 12+300+24. thanks!
Hope someone could help, or maybe its impossible?
Reading Values From A Table Cell?
Does anyone know what the equivalent statements in VBScript would be for the following?
Dim CellValue
Dim ActView As PivotView
Dim fldSets As PivotFieldSets
Dim Sel As PivotDetailRange
Dim RowNum As Long
Dim pvDetCell As PivotDetailCell
Dim PvCell As PivotCell
Set ActView = PvTable.ActiveView
Set fldSets = ActView.FieldSets
Set Sel = PvTable.selection
Set pvDetCell = Sel.BottomRight
RowNum = pvDetCell.Row
With PvTable.ActiveData
Set PvCell = .cells(.RowMembers(0), .ColumnMembers(0))
End With
CellValue = PvCell.DetailCells(RowNum, 4).value
I tried the following to return a cell value:
MsgBox PivotTable5.ActiveData.Cells.DetailCells(0,0).Value
but it gives an error. Any ideas? Thanks.
Setting Cell Values In Excel With VB
I'm trying to set cell values in Excel from within my VB program. If that is possible, how can I do it?
Any help would be much appreciated.
Thank you
How Can I Compare Cell Values In Different Workbooks
Morning,
Could someone please help.
I am trying to do a comparison on cell values on worksheets which are in different workbooks. The problem I am having is that I do not know how to activate one workbook and compare it to another. I can do a compare from one worksheet to another in the same workbook but have not been able to adapt it to look into another workbook.
I am trying to do a check to see if values in workbook1 already exist in workbook2. If the value in the cell range does not exist then copy/paste the cell range value from workbook1 into workbook2 on sheet1 in column1. If the value in the cell range does exist or is a blank cell value do not copy/paste and loop to the next cell in the range on sheet1 in workbook1.
Code that works when working in the same workbook with 2 sheets:-
For rwindex = 11 To 14
For colindex = 3 To 7
Set r = Sheets("sheet1").Cells(rwindex, colindex)
Set r1 = Sheets("Sheet2").Columns(1)
For n = 1 To r.Rows.Count
Do Until r.Cells(n, 1) = ""
For n1 = 0 To r1.Rows.Count
If r1.Cells(n1 + 1, 1) <> "" Then
If r.Cells(n, 1) = r1.Cells(n1 + 1, 1) Then !This works fine Exit Do
End If
Else
With Worksheets("Sheet1").Cells(rwindex, colindex)
If .Value <> "" Then
With Worksheets("Sheet2").Range("a1")
e = 0
Do While e <> 1
Worksheets("Sheet2").Range("A1").Insert Shift:=xlShiftDown
With Worksheets("Sheet1").Cells(rwindex, colindex).Copy
With Worksheets("Sheet2")
.Range("a1").PasteSpecial _
Operation:=xlPasteSpecialOperationNone
End With
End With
e = 1
Loop
End With
End If
End With
Exit Do
End If
Next n1
Loop
Next n
Next colindex
Next rwindex
Sample of code that does not work with 2 different workbooks:-
Workbooks.Open("workbook2.xls")
Workbooks("workbook1.xls").activate
For rwindex = 11 To 14
For colindex = 3 To 7
Set r = Sheets("Sheet1").Cells(rwindex, colindex)
Workbooks("workbook2.xls").activate
Set r1 = Sheets("Sheet1").Columns(1)
Workbooks("row and column test.xls").activate
For n = 1 To r.Rows.Count
Do Until r.Cells(n, 1) = ""
Workbooks("workbook2.xls").activate
For n1 = 0 To r1.Rows.Count
If r1.Cells(n1 + 1, 1) <> "" Then
Workbooks("workbook1.xls").activate
If Workbooks("workbook1.xls").r.Cells(n, 1) = Workbooks("workbook2.xls").r1.Cells(n1 + 1, 1) Then !code errors here
Exit Do
End If
Else
Workbooks("workbook1.xls").activate
With Worksheets("Sheet1").Cells(rwindex, colindex)
If .Value <> "" Then
Workbooks("workbook2.xls").activate
With Worksheets("Sheet1").Range("a1")
e = 0
Do While e <> 1
Workbooks("workbook2.xls).activate
Worksheets("Sheet1").Range("A1").Insert Shift:=xlShiftDown
Workbooks("workbook1.xls").activate
With Worksheets("Sheet1").Cells(rwindex, colindex).Copy
Workbooks("workbook2.xls").activate
With Worksheets("Sheet1")
.Range("a1").PasteSpecial _
Operation:=xlPasteSpecialOperationNone
End With
End With
e = 1
Loop
End With
End If
End With
Exit Do
End If
Workbooks("workbook2.xls").activate
Next n1
Loop
Workbooks("workbook1.xls").activate
Next n
Workbooks("workbook1.xls").activate
Next colindex
Workbooks("workbook1.xls").activate
Next rwindex
Edited by - Raistlin1971 on 8/4/2003 2:33:18 PM
DUMB Question {Comparing Cell Values}
Although I know the basics of programming, I am by NO MEANS fluent in VB code. With that said, I have a very basic problem. All I want to do is write an IF...THEN statement that compares two cell values. Here's what I have:
If ActiveSheet.Range("g44").Value > ActiveSheet.Range("g42").Value Then
...
End if
or should it be:
#If ActiveSheet.Range("g44").Value > ActiveSheet.Range("g42").Value Then
...
#End if
The first one doesn't do anything. The second one gives me a compile error. Would someone please take a minute and get me straightened out???
Adding Data/values To A Cell That Has Allready A Value
hello,
i created an excel application for me and some other teachers to
the teacher gives a score and the userform writes the score to the cell linked to the student.
if the student has a bad score, the teacher can choose from a listbox the reason for the bad score. example "answer incorrect" or "not to the point" or "answer incomplete" and some other reasons...
this works perfect!!
my question : suppose i choose "answer incorrect" and i also want to add "answer incomplete" HOW CAN I DO THIS
because now i can choose only one item from the listbox, if i choose another the previous one is overwritten
Adding Multiple Values To A MSFlexGrid Cell
I have a user form with a number of input boxes i.e. text, listboxes etc. also have a MSflexGrid. I want to join all data entered into my inpot boxes (approx 6 boxes) then add them to a single cell in the flexgrid and finally wrap and resize the boxes so that the text is displayed.
I am making a pigs ear of it, and certain my approach is not the correct one. My first problem is that I have created a text box to join all the values with a space between each, this is fine apart from the time values (these come from a DTPicker control) I keep getting an error message; 'runtime error 13, type mismatch' I have tried using .Text in place of .Value but still doesn't work.
Note; my JoinedText box will already have a number in it to which I want to add the joined text.
my code is as follows;
Private Sub JoinedText_Click()
JoinedText.Text = JoinedText.Text + " " + AddEvent.Text + " " + Location.Text + " " + StartTime.Value + "-" + EndTime.Value
txtCell.Text = JoinedText.Text
End Sub
Secondly, I have 8 Columns in my flexi grid, and want the value above to be placed in the first empty flexgrid cell, the next record to go into the 2nd flexgridcell and so on until all 8 cells are populated. I haven't a clue how to do this, I have managed to get the text into my flexgrid, but it goes in the last cell (column 8) and not the first;
Private Sub txtCell_Change()
MSFlexGrid1.Text = txtCell.Text
End Sub
What is the easiest way to achieve this?
Any advice/help would be greatly appreciated
Are Formulas Unable To Modify Other Cell's Values?
If I write a small function such as this:
Function setValue()
Range("A1").Value = "Hello"
setValue = Range("A1")
End Function
then it works just fine if I simply execute the macro (from the run macro dialog, or from within the VBA editor), and "Hello" shows up in A1. But if try to use it as a formula in a different cell - say, I put into cell A2 '=setValue()', then it returns a '#VALUE!' error, and doesn't set the value of cell A1.
Why is this? Is there some rule that formulas aren't allowed to modify the values of other cells? If so, why, and are there ways to work around this?
Create String From List Of Cell Values
Hi all,
I have a list of cells with values in them e.g. cell A1 = AA, cell A2 = BB, cell A3 = CC a.s.o. for a varying number of rows i.e. the list can include from 1 to an unlimited number of cells but all in the same column. What I need is a procedure that loops through the list of cells and takes the value of each cell and puts them all into one cell, with commas between each value. The procedure should continue until the next cell in the column is blank. The end output, using the previous example, should look like "AA,BB,CC,".
I appreciate any help any of you can offer.
tb
Excel Problem (Matching Cell Values)
Hello to all...
now here is some thing interesting
the problem (although not a big problem) is that i have to take the data from two seperate sheets of Excel (in a single file)
and this data is in the form
suppose
Lahore-Karachi-34 (this is in sheet1 in a sigle cell) suppose in cell A1
and in the other sheet sheet2, the equivalent data to sheet1 for cell A1 can be in the form
Form 1 is Lahore-Karachi-34 (i.e. as it is)
Form 2 is Karachi-Lahore-34 (both cities shuffled a bit)
Form 3 is Karachi- Lahore-34 (a space before Lahore)
and other forms... you can guess what
the goal is to compare the data all rows i.e. A1 to A1000
of one sheet1 with other i.e. sheet2, and if any record
of sheet1's any row has been found in sheet2's any row then
suppose a word "Found" should appear in cell next to sheet1 for each row found
for more queries plz reply at Khalid_baoo@hotmail.com, khalid_pk@hotmail.com
Thanks in Advance
bye
|
TRACKER
