here i have one scrip name ABB and there are 4 rates for the given thing i need thats if on a different sheet i write ABB i want to get the higest rate and the lowest rate in the next Colums as result so plz help me ,with the formule that i should be using for the required thing to get my out put.
Answer:-
Assuming that the data is in A2:G5...
Control+shift+enter, not just enter:
=MAX(IF($C$2:$C$5="AAA",$G$2:$G$5))
=MIN(IF($C$2:$C$5="AAA",$G$2:$G$5))
This was the answer which i got this converts into array my formulae but then i am facing a problem when i save my excel sheet then my answer to the solution is there but the formule doesnt appear when i reopen my excel sheet so i cannot make use of it again i have to again write the formule so i need a solution where once the formule is writen then is should not diappear when the file is saved and closed .
I have made macro that works but I want to convert it to array and want change all dates with looping. I have used based on selection and dates to convert are not always in Column E
So I am starting to get ambitious with Excel now and am looking at creating a macro that will automatically create a pdf file of several worksheets at the same time. However this is my problem. Using the current version of Excel there is a wonderful record macro button, however when I try to include a function that would create a PDF, it says that I need to save any changes before creating the PDF. However when I save the file, I have to stop recording which defeats the whole purpose of creating the macro in the first place.
How can I use the record macro function to create my pdf macro without learning visual basic? If I cannot, how to write what it is that I am trying to accomplish?
Someone here wrote up this code for me which converts cells with formulas to a new formula (It adds a division to the formula in the cell). It works great with one problem. If the code is rerun multiple times it doesn't check if there is a division added already and just keeps adding and adding to existing formula. For the last our I tried to add a check to the code. Something along the lines "If cell already has "/H2" inserted" skip to next cell. But nothing is working.
Here is the code I'm working with:
Sub Macro2()
Dim MyRange As Range, NewFormaula As String, Delim As String Dim Cell As Range Dim MyString As String Dim Format As Range Dim ws As Worksheet
I extracted data going back to 1980 from Bloomberg and realized that BB uses abbreviations rather than pure numerical values. For example, 3,000,000 is "3M," 300,000,000 is "300M," etc. I need a macro that converts "M" into the actual value, "B" into the actual numerical value, etc. For example, a macro that converts "3M" into 3,000,000. I know that I can identify the values in VBA with "###M."
i have a created a connection with Access table named Test, the table has 3 fields, ID,Firstname and LastName. The values in field firstName is filled in a Combox which is in Sheet1 of excel sheet.
so far i have done with the coding which create a connection with access table and fille all the firstname from the table to the Combobox using a recordset.
the code for filling combobox value from records set is like
Do Until RecordSet.EOF shp.ControlFormat.AddItem RecordSet.Fields("Name").Value RecordSet.MoveNext Loop
Now what I need is whenever I select a name in a combobox the Cell A1 in excel should show me the LastName for that name.
i have a select statement, that pulls one row from access and i need to get the data and assign it to different fields in a userform. however, because it only pulls one row, its at .eof and closes, so any following statements cause errors because the recordset is closed.
I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code.. But not working..
Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.
The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.
When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.
i've tried doing a pastespecial format:="CSV" but it doesnt work.
Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??
I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table!
What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow!
In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....
Is there an example outthere that will demonstrate how to create a pivot table using an ADO Recordset? I'll be experimenting tommorow but really don't relish the thought of spending 12 hours trying to figure it out.
When I read a CSV file into a recordset, the headers are not all being read. I get gaps in the headers for whatever reason. Attached below is the Function to load the recordset from the file. I have hard coded Hdr to "No" in the connection string.
Code:
Function QueryByID(tableName As String, fieldToQuery As String, Target As Long, Hdr As String) As Recordset Dim strFilePath, strFileName, strQuery As String Dim oFSObj, oConn As Object Dim oRs As Recordset Dim f, lastRow As Integer
[Code] .......
The below is how I output the resultset:
Code:
Sub OutputResultSet(aTable As String) Dim row, col, i, j As Integer row = 1 col = 1 ThisWorkbook.Sheets(aTable).Activate
[Code] .....
I have triple checked the CSV file and the first row in each file has definitely got headers. Some headers are loaded and output fine, others are just blank.
how i can display different values using the Selection.FormulaArray, ok look at my Dim mySheet As Worksheet
' On Error GoTo errors Dim x As Integer x = TextBox1.Text Dim y As Integer y = RefEdit2.value
Set mySheet = Excel.ActiveSheet mySheet. Range(RefEdit1).Select
Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)" MsgBox Selection.FormulaArray
Now the function i call:
Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer multiplie_value = Val(valor) * multiplo End Function
thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.
I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code: Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sQRY As String Dim strFilePath As String
I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.
The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.
*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.
Code: Sub RunExistingQuery() 'Execute prestored queries in Access Dim Con As ADODB.Connection
I'm looking for an example of creating a pivot table based on an ADO Access Recordset.
I understand I can use MS Query - which I am, but looking to see if an alternative exists. A table I am inetrested in currently has 400,000 records I would like to retrieve and pivot.
I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg...
however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column
I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.
So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.
Here is the working simple query to retrieve a single row:
Code: dupeSQL = "SELECT PSID FROM [Obstructed$] "
Here is the working single query to retrieve a count:
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
