I have got one column with 250 rows. The data fed is 0 and 1 randomly in these rows. I need to find the number of row between any two 1's. I need to write a macro for the same.For ex: if my column is as follow: A1 0 B1 2 A2 0 B2 2 A3 0 A4 1 A5 0 A6 0 A7 1 A8...
I have created a VB.NET 2008 Class Library that has, for the most part, been working quite well with VBA. I can perform most of the functionality that is required but I have recently run into a pretty severe problem. The reason we are working with both programming interfaces is because we need to be able to do "on the fly" coding and th...
Hello, I want to count no of different cells which are selected using VBA. Consider if we select five distinct cells - D5, C2, E7, A4, B1. Is there a way I can count these number of cells. Secondly how can I retrieve data in these cells. Lets say I want to store it in an array. Thank you for the help. ...
Please can somebody help me with the correct DIM statements and syntax to simplify the following into a DO UNTIL loop?: Sub DesRisk_Loader() Dim Qn(7) As String Dim Ys(7) As String Dim No(7) As String Dim Wk(7) As Integer Application.ScreenUpdating = False n = 1 x = 1 Do Application.Goto Reference:="DesHome" ActiveCell.Offset(x, ...
My Delphi 6 program need to place an image on each row of my of Excel Sheet. I could insert a picture to a fixed position with something I read from another post. procedure insertImages(ActiveSheet: OleVariant; ImageFilePath: String; ImageHeight, PictureTop, PictureLeft: Integer); var Picture: OleVariant; begin try Picture ...
One of my users wants to get data into Excel from SQL 2008 query/stored proc. I never actually did it before. I tried a sample using ADO and got data but user reasonably asked - where are the column names? How do I connect a spreadsheet to an SQL resultset and get it with column names? ...
i have got a question about referencing a cell in other worksheet. I have got this code in VBA: If Application.CountIf(.Rows(Lrow), '8000')= 0 And Application.CountIf(.Rows(Lrow), '9000')) = 0 Then .Rows(Lrow).Delete Which is capable for me to delete any row WITHOUT words 8000 and 9000. However, since there would be future update, how ...
Hi guys, I get an error message that is annoying me for a long time and could not fix it. I am writing a code in VBA Excel. Suddenly I got the following message: Excel found unreadable content in [workbook name]. then it asks me do you want to recover the content? Please help me to solve this, Thanks, ...
I am Generating a New Sheets using macros. For a New Sheet generation , Data is retrieved from more than 4 MS Access DB. Each DB had minimum 200 field. My Macro code includes 1. Cell locking 2. Alignment and formatting 3. One third of the cells in the sheet had a formulas 4. Cell reference with other Workbooks My problem is e...
The worksheet is sorted base on Column A (account number) first and then Column C (date). I have below script that remove duplicate records and remain the most recent one. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("a1").End(xlDown).Row To 2 Step -1 Do While Cells(RowNdx, "a").Value = Cells(RowNdx - 1, "a")...
There are 3 sheets in a workbook: Sheet1, Sheet2, Sheet3. Sheet1 has the following data: aaa 3 aaa 2 aaa 45 aaa 211 aaa 12 bbbb 3 bbbb 2 bbbb 4 ccc 2 ccc 5 dddd 2 dddd 10 dddd 25 There will be a hash table like this: key values GroupA aaa, bbbb GroupB ccc, dddd How can I load data t...
I would like to execute some code when a user closes a form using the x button in the top right corner of the window (I have the form load when the excel spreadsheet is opened, and it hides Excel. I want to exit excel once the form is closed, or at least show excel again so the user may exit it manually) Looking at the form properties, ...
Problem: I have data that looks like this and I want to compare the rows to the column values. so is(Table1.t1 = Table2.t1)? kind of thing. Table 1 A B x t1 x t2 x t3 x t4 Table 2 A B C D E x t1 t2 t3 t4 Can I do this in Excel with functions or VBA ...
hello! This task is driving me mad... please help! Instead of manually type in the data, i have used VBA to find the year range, put into one column and delete all duplicate ones. But since excel could give more than 20 years, it would be tedious to do all the filtering manually. AND, now i need excel to separate the rows that contain th...
Hi, I have a excel dashboard which works such that before the excel file is closed, I would like to display all the EXCEL ribbon, so that next time excel is opened, the application / excel will show the ribbon. At present, it does not show the ribbon if excel is opened. Private Sub Workbook_BeforeClose(cancel As Boolean) On Err...
hello guyz, im new at macro excel this is my first experience with it. i m just experiment with it & making my office process easier. i almost completed the project but in our process data should matched with two columns usually we done it by sorting but while im operating macro there is no option to put tab between unmatched data im ...
Hi, I developed a excel add ins using VB.NET 2005. and in this when i adding page break below error is occurred :- This action exceed the number of page breaks you can manually add to a worksheet. a worksheet can contains up to 1026 horizontal page breaks So can any one suggest how i insert more than 1026 page break or any other s...
I have a macro that gathers data and creates a scatter plot and that is saved to a new workbook. Each individual point in the plot has a label (DataLabel). Some of the points are so close together that the the labels are not visible. The user just simply moves the labels around with their mouse. The macro also changes a few things in th...
I'm trying to create a dropdown that upon changing the selection from the list of options will run a query that will insert the query results into the page. Here's what I have thus far: Sub DropDown1_Change() Dim dbConnect As String Dim leagueCode As String Dim leagueList As Range Dim leagueVal As String Dim TeamDat...
I am trying to get resultset from SQL 2008 sproc into Excel 2003 using VBA. It worked for few sprocs but when I tried the one which uses temp table or table variable VBA fails with err 3704 "Operation is not allowed when the object is closed" on the following line: Sheet1.Range("A2").CopyFromRecordset rsMyDB If I comment out select i...