How To Shade Cell In Excel Based On The Date Value In The Excel Column

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,


suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.



Code:

VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook  Screen.MousePointer = vbHourglass  Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce%  For i = 0 To MSHFlexGrid1.Rows - 1     For j = 0 To MSHFlexGrid1.Cols - 1                     If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then             MDur = MSHFlexGrid1.TextMatrix(i, 55)             MDur = MDur / 30             wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")             wsheet.Cells(i + 2, j + 1).Font.Bold = True             obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed             obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow             For Ce = 0 To MDur                              Next         ElseIf j > 7 And j < 55 Then             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)             wsheet.Cells(i + 2, j + 1).Font.Bold = True         Else             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)         End If     Next Next  For i = 0 To 1     For j = 0 To MSHFlexGrid1.Cols - 1         wsheet.Cells(i + 2, j + 1).Font.Bold = True         wsheet.Cells(i + 2, j + 1).Font.Color = &H800000     Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2")     .Select     .Merge End With  With obj1.Sheets(1).Range("T2:AE2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AF2:AQ2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AR2:BC2")     .Select     .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub




also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.

Thankyou,
Chock.

---

Conditional Formatting In Excel Based On Specific Cell Updates
Hello,

I am creating an excel spreadsheet to track work progress and am having difficulty in setting up some conditional formatting on a column.

I have a header column as below:

Owner Coded Reviewer Review Number Status


Now underneath each of the above column headers, the data entered in each of the cells has an effect on what happens in the status column.
The status column has the following formaul entered in each cell:


Code:
=IF(J4<>"",IF(K4>=1,IF(M4<>"",IF(N4<>"","Ready for Rework (Post Review)","Review in Progress"),"Ready for Review"),"Schedule in Progress"),"Not Started")
Now for each of the 5 states which the formula can set in the status column I want to associate a colour - different to each other.

To do this i have created the following VBA:

Code:

Sub UpdateStatusColumn2()

Dim s As String

For Each c In Worksheets("Sheet3").Range("O2:O909").Cells

s = c.Value


Select Case s

Case s = "Not Started"
c.Activate
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

Case s = "Schedule in Progress"
c.Activate
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

Case s = "Ready for Review"
c.Activate
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Case s = "Review in Progress"
c.Activate
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With

Case s = "Ready for Rework (Post Review)"
c.Activate
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Case s = "Closed"
c.Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With

End Select

Next

End Sub
My spreadsheet has almost 1000 rows and hence I do not want this to check every row when called.

What I want is for when the Owner, Coded, Reviewer, Review Number, or Status cells are updated for a particular row, then the above procedure UpdateStatusColumn2 is ran only on the same row for the cell in the Status column.

How do I do this? I know the for loop a the start will require updating as it will now no be required, but how do I get VBA to run the procedure only a single row when a cell underneath Owner, Coded, Reviewer, or Review Number is modified?

I should also say that I tried to use the Conditional Formatting within the Format menu but it only lets me use 3 conditions - which is not enough

I would appreciate all help offered.

Thanks,

W.

---

Count No Blank Cell In Column AC Based Var In Column Y
With acode in VBA please....

How to count all non blank cells in column AC based a Var filled ="PUGLIA" and return the result of count in var My_count...
In thsi case the coun = 6
I use this formula but difficult to translate in VBA....

=MATR.SOMMA.PRODOTTO((Y3:Y1000="PUGLIA")*NON(VAL.VUOTO(AC3:AC1000)))

---

Find Last Cell In A Column In Excel
Edit: Re: http://www.xtremevbtalk.com/showthread.php?t=289692

What type is x1Up?



Edit by Moderator:
Please post your own threads, to ask your own questions, posting links to other threads where required.

Thank you.

---

Find Last Cell In A Column In Excel
I have found an old thread at this site which gives a number of ways to use vb to find the last cell in a column in an exec spreadsheet. I have tried them all but none works.
for example, if I use

Dim numP1 As Excel.Range
Dim XLWS As Excel.Worksheet

numP1 = XLWS.Range("A65536").End(-4162)

or

numP1 = XLWS.Range("A65536").End(Excel.XlDirection.xlUp)

I always get a runtime error "Object variable or With block variable not set"

Could someone tell me what I am missing please?

Thanks
Chris

PS I am using VB6 and Excel 2003


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

---

Excel Cell/Column Format
Hi Friends,

How can i change/set the column format thru VB.

I want to set date format to one of the column while transfering data from flexigrid control to excel file.


Thanks.

---

Move Cell In Column B Based On The Value In Column B
I have an Excel spreadsheet that is updated daily. I need a macro that can

1) create a new column between column B and C
2) if the value in column B is 1, then move the value of the cell in column C to the newly created column
3) find the next instance of column B = 1...again move then move the value of column C to the newly created column...repeat until the end of column b

---

How To Read An Excel Spreadsheet Column, That Is, Read From The Top Cell To Bottom Cell (like File R
How to read an excel spreadsheet column, that is, read from the top cell to bottom cell (like file read to eof)? how can I know which cell is the last cell?

thanks

---

Deleting Rows In Excel Where A Cell In A Certain Column Equals Zero
I'm looking for an excel macro to delete a row if a number in a cell equals zero in a certain column. For example, what would be the code to delete all rows where the numbers in the cells in column "L" are equal to zero?
Thx

---

Comparing Each Cell In Two Excel Columns, Print The Result In Third Column
Hi there,

I would like to compare each cell in two columns, and print the result of the comparison in the third column.

I have done the following:

For each x in Range("B1:B1000")
For each y in Range ("H1:H1000")
if x=y then Msgbox "Match"
Next y
Next x

My problem is, I am not sure on how to print the result in a third row. In other words, I am not sure on how to insert the right codes to replace the code Msgbox "Match".

Thank you so much

P/s: I am referring to macro programming in Excel. Thanks.

---

Excel VBA- Date From Cell Into Combobox
I tried to get the date from excell cell into combobox on form in VBA.
The cell has formula '=Today()'.
The combobox displays the date, but when selected, it turns out to be a number in the box. The ConrolSource is another cell where it displays the number.
I tried with formatting the cell in excel to date, but still the same result.
What is the solution to display a date in date format in the combobox & ControlSource cell?

---

Insert Date In Excel Cell
Just a quick question and i know it sounds dumb ,but how can I strToday have enter the date only in a cell and not date and time?

Code:
    Dim strToday As Date
    strToday = Now
    ActiveCell.FormulaR1C1 = strToday
    Range("D9").Select


Cheers

 --
Lobo

There are 10 ways of doing things.. Right or wrong.

---

Trouble Extracting And Using Date From Excel Cell
I'm pretty new to excel vba and I have a macro that I want to extract the date out of a cell and use it to match up with another date in one column.

    Dim tempDate As Double
    tempDate = Cells(1, e).Value

    For tempRow = 1 To 35
        If tempDate = Cells(tempRow + 60, a) Then
            rowIndex = tempRow + 60
        End If
    Next tempRow

All I'm doing is trying to set tempDate equal to the contents of cell E1 (which is a date mm/dd/yyyy). Then I want to search column A for the date and store the row that matches in rowIndex so I can use it later in the subroutine. If anyone has a better way of doing this also, PLEASE let me know!!

Thanks a bunch!
J

---

Sending Rows From One Sheet To Another Based On Cell Value In Column A
Hi everyone

I've been racking my brains since yesterday to come up with an answer to this but to no avail so am hoping that one of you lovely people can help me out.

What it is, I have a worksheet, "Data", that contains 7 columns, and lots of rows (will be different each time report is run). I also have five other worksheets, Revenues, Benefits, etc etc.

Column A in the Data sheet are Team Managers names. Each team manger is from a specific service. What i want to do (by poss a comand button on each of the other worksheets?), is to copy the the rows from the data sheet based on whether the team manager relates to that service, i.e.

1 Joe Bloggs
2 Joe Bloggs
3 Bob Smith
4 Andy Jones
5 Andy Jones

So if Joe Bloggs is on revenues, then I want rows 1 and 2 to be copied to the sheet Revenues, Bob Smith is on benefits so I want row 3 to be copied to the worksheet Benefits. Then Andy Jones is also on Revenues so I want rows 4 and 5 to be copied to the Revenues sheet, being careful not to overwrite the rows that have already been copied there.

I know you're probably thinking why can't this be done by copying and pasting but there is sometimes in excess of 2000 rows and this would take a very long time to do.

Any help on this and I would be extremely grateful.

Cheers
Lindsey

---

MSFlexGrid Column Width Based On Cell Text
How can I automatically set the column width of a column in an MSFlexGrid to be the same size as the text in the currently selected cell. I want the cell to show the entire text.

-Mark

---

Excel: Cell (Due Date) &lt;= Date
Hello everyone,

I would like to have this following codition happen in an Excel Sheet.


Code:
Private Sub Form_Load()
If (txtDueDate.Text < Date) Then
txtDate.ForeColor = vbRed
End If
End Sub

The code works nicely in VB, but since the codes are different in Excel, I couldn't still get it to work.

I hope you guys can understand what I expressed in the VB Code.
Just like Form_Load() in VB, I would like the macro to run when the Excel sheet starts.
txtDueDate in VB Form, is like a Cell (e.g. A1) in the Excel sheet.

Desc: The user inputs 31/11/2003 as the Due Date of a task in cell A1.
As the time passes and 31st of November actually arrives, that A1 cell color becomes Red.

Any help to get this code in VBA for Excel, will be greatly appreaciated.
Thank you very much.

---

Excel Macro --&gt; Link *.foo File To Cell And Update *.foo Date Time
Hey All

Could someone show me a quick way to program two macros. These will be used in Excel to help me automate many of our tasks.
The first one would simply open up an "explorer" window and show only *.foo files. The directory should always point to the same directory everytime it's called. In other words, when I first run the macro I point it to W:, and every subsequent time after that it points to W:. Secondly all I want from the file is the Name and Location. I don't want to open the file. Basically a hyperlink to the file, that when depressed would be opened by the default extension. (i.e. .txt -&gt; notepad)
The second Macro would get the Name and Location from the above macro and return the Creation Date and Time. And place that in the cell.

Again thanks so much.



------------------
Steven M. Klass
Physical Design Engineer

Andigilog Inc.
7404 W. Detroit Street, Suite 100
Chandler, AZ 85226
Ph: 602-940-6200 ext. 18
Fax: 602-940-4255

sklass@andigilog.com
http://www.andigilog.com/

---

Vba - Increasing Cell Value Based On Date
everytime i have a problem with vb, it always gets solved at vbcity, so here i am again with another problem.

ok, so here it is:

i have some cells with multiple values in them, and on the 1st of every month, i want interest to be added at 1/2 %. and other than getting the current date, im not sure what to do.

i realise its vba problem, but its quite a newbie problem so i posted it here.

any help is much appreciated.

Primary_Slave

---

Cell Contents Based On Date - Are VBA And VB6 Similar?
Hi All,

I am quite a competent vb6 programmer. I only really dable is the small programs. I have never programmed using vba though. Are the languages of vba and vb6 similar?

I am trying to program a vb macro that will alter the contents of some cells on launch. Basically one column has the purchase dates of some items and the next column has the age status. If the item is under 3 years it should say green, 3 - 5 years = amber, over 5 years = red.

I can't quite get my head around the intergration bit though. Are there are resources that you would recommend me reading?

Thanks
Your Humble
SpaceMonkey

---

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

---

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way


Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"


however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

---

Vb/Excel: How To Read An Excel File In A Column WITHOUT Specifying The Range ??
Hello, all.

I found a bit of code that lets me read an Excel file from and to a specifi
range. See the code below. But what if I only want to specify the STARTING
position and simply want it to continue reading down the column until it
hits an empty cell?

Does anyone know how to do this? Thanks!

Here is the range-only code:

' ** turn screen updating off, prevent the user from seeing the source
workbook
' ** then find the file and identify both the worksheet and range
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open(file, False, True)
listitems = sourcewb.Worksheets(sheet).range(temp_range).Value

' ** close the source workbook without saving changes, then reactive
screen update
sourcewb.Close False
Set sourcewb = Nothing
Application.ScreenUpdating = True

' ** read the data, convert values to a vertical array
listitems = Application.WorksheetFunction.Transpose(listitems)

For count = 1 To UBound(listitems)
' ** read the info into a temporary field
temp_var(count) = listitems(count) ' ** populate the array
Next count

' ** no items selected, set to 0 to select the first item
'Combo_machine.ListIndex = -1

Close Workbook

---

Can An Excel Column Be Passed Into A VB Array In Excel
Is it possible to pass a say 5 values from the same (different) columns in excel into an array using VB?

This is the basis of the code

Private Sub Button1_Click()
Dim numbers() As Integer
Dim txt As String
Dim i As Integer


' Load the data.
LoadNumbers numbers(), Array((Worksheets(1).Range.Cells("d2:d8")))
' Display the values loaded.
txt = ""
For i = LBound(numbers) To UBound(numbers)
txt = txt & Format$(numbers(i)) & Chr$(10) & _
Chr$(13)
Next i
MsgBox txt
End Sub

' Copy numbers from a variant array to an integer
' array. This will give better peformance.
Private Sub LoadNumbers(arr() As Integer, ByVal var_arr As _
Variant)
Dim min_num As Integer
Dim max_num As Integer
Dim i As Integer

' Make room for the numbers.
max_num = UBound(var_arr)
min_num = LBound(var_arr)
ReDim arr(min_num To max_num)

' Copy the numbers.
For i = min_num To max_num
arr(i) = var_arr(i)
Next i
End Sub

---

Excel - Making A Cell Equal To The Value Of The Formula In The Cell
I thought I'd seen something on this already, but I coudln't find it. My appoligies if I'm creating a duplicate thread.

I'm writing a macro in excel to fill in data in a database. Unfortunately, my titles for each entry have to be taken from multiple cells and put into one cell. The code to place the title in the title cell is:

Code:Dim title As String
    title = inputname & "!$B$3, " & inputname & "!$C$3, " & inputname & "!$D$3, " & inputname & "!$E$3, " & inputname & "!$F$3, " & inputname & "!$G$3, " & inputname & "!$H$3, " & inputname & "!$I$3"
    title = "=CONCATENATE(" & title & ")"
    ActiveCell.Value = title

It works fine. The problem I'm having is that every time I save and reopen my database excel pops up with this "The workbook you opened contains automatic links to information in other workbooks..." It's getting very annoying. And it also gives me problems if anybody touches the already filled title cells. I was thinking that there might be a way to make the formula equal to the value of the formula. For example making a cell have the value 4 instead of the forumula "=2+2". Thanks for any help.

---

How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.


I used these to get my excel info

Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value

Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"

help!!!

---

How To Convert Scientific Value Of Excel Sheet Column To Number Column In Vb?
Hai Experts,
I have two columns in excel sheet with scientific value (Eg:5E+11).I want to convert this value into number --like 500000(not specifically this no this is an example) using visual basic

Can anyone of u tell me how to do this conversion process?

Thanks...

---

Excel Issues - "Excel Cannot Determine Which Row In Your List Contains Column Labels"
Hello again,

I am working on a program that imports various Excel spreadsheets into one one workbook. The data is then sorted and calculations are made which are then summarized into one sheet. I have created a form that will enable the user to select which kind of charts they would like to generate and for what, all based on the summary of the data.

The issue I am faced with is an Excel prompt that comes up whenever I use the subtotal function for my calculations. The prompt states "Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command." I use the subtotal function on a number of different worksheets, so this prompt is coming up 5 times.

The imported data does have bold column headings as well. Is there a way to force these prompts to say OK which tells Excel to use the first line of data as my column headings without these prompts coming up? Or is there any other quick fix for this? Any advice is very much appreciated.

---

Dynamically Selecting Column By Excel Column Header (B, C... AD, AE, Etc...)
Hi Guys,

I am trying to automate a spreadsheet which would copy values from one worksheet, and insert it into another after manipulating the values slightly. I have managed most of the code (as shown below), however, I am struggling with the following:

The spreadsheet contains pricing information per supplier, and my aim is to retrieve the part number, size, and cost per part from the original into a new worksheet. As the cost column may be in a different location (based on multiple suppliers in the same worksheet), i need to prompt the user for the worksheet that contains the information and the column that contains the costs. I have already written the code to parse the worksheets and populate the combo box with worksheet names. Once a worksheet is selected, I need to parse all used columns and populate a combo box with the Excel column headers (e.g. B, C... AX, BE, etc...) so that the user can choose the correct column. Once the column is selected, I need to convert the column header to a column number (e.g. Col A = 1, Col F=6, Col BC = 46) so that I can copy the values via the GetRows() subroutine in the code.

Any help would be appreciated. Included below is the code that I have thusfar...

Thanks

-------------------------------------------------------------------------------------------------


Code:
Private Sub Worksheet_Activate()

' Get number of worksheets in workbook
cboWorksheet.Clear

' Get names of worksheets in workbook
For iCnt = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(iCnt).Name <> "Data Unload" Then
        cboWorksheet.AddItem ActiveWorkbook.Sheets(iCnt).Name
    End If
Next

End Sub

Private Sub GetRows()

' Setup variables to be used
Dim strNewPart, strNewSize, strSheetName As String
Dim iRowData, sRowData, x, y As Integer


strSheetName = cboWorksheet.Text
    
For i = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(i).Name = strSheetName Then
        x = i
    End If
    
    If ActiveWorkbook.Sheets(i).Name = "Data Unload" Then
        y = i
    End If
Next
    
    
'Setup Variables in Use
iLastRow = ActiveWorkbook.Sheets(x).Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
sRowData = 6
Debug.Print "iLastRow = " & iLastRow
Debug.Print "sRowData = " & sRowData

       ' Loop through the data table and compare each record with data in the lookup table
       ' If a match is found, change the data table with what is in the lookup table
       For iRowData = 8 To iLastRow
           If Worksheets(x).Cells(iRowData, 1).Value <> "" Then
              
              If Worksheets(x).Name = "Product X" Then
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "X"
                  strNewSize = "0.0"
              Else
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "Z"
                  strNewSize = "0.0"
              End If
              
              Worksheets(y).Cells(sRowData, 1).Value = strNewPart
              Worksheets(y).Cells(sRowData, 2).Value = strNewSize
              
           End If
       
       'increment Unload Counter
       sRowData = sRowData + 1

       Next

       Worksheets(y).Select

End Sub

---

Excel - Return Column Alphabet From Column Number
Is there some function in Excel (VBA) that will get me the column alphabet if I provide the column number, or do I have to write one. I ask because I know that there is, yet I recall I wrote this functionality for the previous application, and I don't want to:

(1) Reuse it, if there's something in-built
(2) Re-invent the wheel

Help!

PS: I'm looking for something like:


Code:

Function GetAlphabet(ByVal ColumnNumber as Long) as String


which if I call must say:


Code:

GetAlphabet(1)


Output: "A"

---

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!

---

Track Cell For Highlight Based On Cell Itself, Not Row Number
I am highlighting a cell in each row as that row is selected, and 'un'-highlighting the previous one. Right now I'm doing it based on the row number, but a problem occurs when I sort my columns. Obviously, after the sort, the row number of the highlighted cell changes and this makes the previous row and the current row the same sometimes, so it doesn't take the backcolor off when it should. Is there a way to keep track of which is highlighted by the cell itself, instead of the row number?

---

Excel VBA Deleting Based On Criteria
Okay, now I have a good one.

I need to do the following

run through a spreadsheet looking for an instance of criteria, i.e.,

Name Place This criteria can be part of a larger value in the same cell i.e., Name Place Day Month zip, etc....

Then after it is found I need to delete all rows below it until I reach a cell that is formated BOLD. then I need to stop the delete process and do the search process again.

---

Move Row Based Off Criteria In Excel
Hello All,

What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this:

Code:

Sub testmove()
Dim c As Long
For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5
If Cells(c, 6) = "Closed"
Rows(c).Cut
End If
Next c

End With
'find last row
LastRow = Cells.Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row

???????? ok I'm lost..



I wanted it to find the last row on the closed tab and insert copied cells

Please help..

Any Ideas???

Thanks

---

Creating A DLL For An Excel Based App (RESOLVED!!!)
I want to build a DLL to be used by my Excel based application. The only question I have is:

How do I get data from the various workbooks in my application without having to open Excel in code as it will already be open when I am using this Excel based application?

I have developed a DLL for this app already but I could pass in all the data I needed as parameters. Here I need to search ranges and stuff

I don't want to do this if I can avoid it:


Code:
Dim xls As Excel.Application
Dim xlbook As Excel.Workbook

Set xls = New Excel.Application
Set xlbook = xls.Workbooks.Open("C:PORTMG32datafile.xls")


I've tried this question and searched for other question on this but I haven't seen anything that helps. so I may be screwed but it is worth another try

Thanks in advance

---

Another Rubbish Excel Based Question ...
I have a spreadsheet which has x amount of total lines. I want to Boldface all the total lines, so I have got the following code to do this :


VB Code:
' Boldface the Total Lines        .Cells.Find(What:="Totals", After:=ActiveCell, LookIn:=xlFormulas, LookAt _            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _            False).Activate        .Selection.EntireRow.Font.Bold = True        .Cells.FindNext(After:=ActiveCell).Activate


Now this will find the first instance, boldface it, and then find the next. I want to put this in some kind of loop, so that it finds and bolds them all, but don't know how !

Can anyone help out a thicko ?

---

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

---

My MS Excel Based Utility Package For Everyone's Free Use
Hi Everybody

I am attaching herewith a zipped file for an Excel AddIn (including help files, overview and install/uninstall documentation etc.) that I have written to build some utility tools - that are not available in the standard Excel (to the extent that I know of).

Nothing is protected in the code. Feel free to use these tools provided whichever way you want - from the front end in your worksheets, from the back end in Visual Basic for Application (Excel) modules in your workbooks or in your own utilities that you might be building and may want to include these facilities therein.

Whilst I think I have taken all the care in developing them, please let me know if you do come across any shortcomings &/or inaccuracies therein that you may have encountered (I would like to fix any that may have remained – maybe I am just too close to it to fully conceptualise, program and test the whole package by myself!).

Further, I accept that the coding quality is poor and there is room for improvement (a lot) - reason being that the primary effort had been to get it working as I had intended with 'coding quality and operational efficiency taking a slight back seat'. You might also find some redundant code therein. These are there only because the philosophy adopted may have changed along the way and I may have chosen the better of removing them. Whilst they may be safely deleted, they don't hurt being there, hence haven't put in the effort to remove them. Barring that, all yours.

Any feedback (good/bad/indifferent) is more than welcome! Whilst you are absolutely free to do whatever you like with it (I have, by choice, made it available for free use by all), should you find it useful and beneficial, your acknowledgments would be highly valued - I have put it a lot of effort to get here, along with a great deal of help from a lot of people predominantly on this forum and some on other forums as well.

For all the help that I have received predominantly from this forum, this may be considered as my modest contribution to this forum (I sincerely hope that those who choose to use it would be considered it worthwhile). I remain thankful for having learnt all that I have by being a member of this forum

I am reproducing the contents of the ‘overview’ file to give an overview of the contents of this package as a ready reference.

Starts here :-

I have attempted to provide five (5) additional functionality to extend the built-in Excel to give you additional tools to further enhance your operating efficiency. An overview of these utilities is provided in the paragraphs below to assist you in assessing its potential benefit to you. Should you feel that these may potentially offer you some benefit, please follow the instructions provided to install the same. Once installed, you can use any of the additional functionality. These are :-

1. RETRIEVEIT4ME()
A custom function providing a more flexible version of the built-in VlookUp() function. It is intended to remove most of the limitations of the built-in VlookUp() function viz :-

Ø the search entry having to be in the first column
Ø returning the value corresponding to first found value
Ø search range having to be one contiguous range in an open workbook only

It is intended for this function to work across multiple search ranges. If these search ranges are specified as named ranges, then each of these named ranges must be in an open workbook. However, each of these named ranges can be multiple area ranges with no limitation on the number of areas therein – with the exception of the limitation imposed by Excel itself on the length of the formula text. If specified in the "A1" notation, each of these ranges must be specified as one contiguous area. These can however be referred ranges in a closed or open workbook. One of the limitations of this custom function is that it will only return corresponding values to exact matching values. For full operating details of this function, please refer to the “Custom Help” provided.

2. TOTAL4IFS()
A custom function providing multiple conditional sum. There is no equivalent functionality that I know of for this functionality. The closest built-in function that I can see to this is the SUMPRODUCT() function, which whilst unquestionably one of the more powerful tools in the built-in Excel, suffers from a serious limitation of being able to work across only one (1) contiguous range. It doesn't work across multiple area ranges even when specified as named ranges. This functionality for starters, removes this limitation and then goes beyond to provide added flexibility to a very large extent. I have tried to make it highly flexible and generic and consequently extremely powerful (within reason) and am of the opinion that it goes some distance in offering significant reduction in the primary data redundancy/duplication often becoming necessary to overcome the limitations of the built-in functions.

It is intended for this function to return a summed up value from a specified column or all the columns within the specified range (with the exception of the columns where the lookup entries are to be searched in). The lookup entries are searched for in the specified column(s). These search columns can be either 1 or 2 depending upon whether or not it has been specified to search for a specific number of lookup entries (less than the total number of lookup entries) or all the lookup entries in a specified search column. These parameters can all be specified by entering valid values for the required arguments of this function. The comparison operators for each set of lookup entries can also be independently specified and influence the values to be summed up in the final result. For full operating details of this function, please refer to the “Custom Help” provided. Given the degree of flexibility if offers, it may require of you, some serious effort to utilise its full potential.

3. GENERIC SORT
A worksheet sorting facility with no limitations on the number of columns that your data can be sorted by. The sort order for each of the sortby columns can be independently specified. It removes the limitation imposed by built-in Excel in permitting only three (3) sortby columns. For full operating details of this function, please refer to the “Custom Help” provided.

4. TXT2XLS
A custom utility primarily targeted at those requiring to handle and/or process large amounts of data predominantly as an output from a primary data source such as a data base. It is intended to enables you to read a text (.txt or .csv - comma delimited) file and dump the delimited data in to a workbook in as many worksheets as necessary with no worksheet containing more than 65000 records. Valid records can be sorted on any number of sortby columns, if desired. The sort order for each of the sortby columns can be independently specified. For full operating details of this function, please refer to the “Custom Help” provided. It may or may not save a lot of time depending upon the size of the data set being handled but certainly goes a long way in reducing the physical effort required to accomplish the tedious task manually.

5. VBA ARRAYS SORT TECHNIQUES (MERGE & QUICK SORTS)
A custom utility primarily targeted at VBA programmers requiring to sort large amounts of data stored in VBA 2 dimensional (rows & columns) arrays. They provide ready to use facility to sort VBA arrays fast & use among the two (2) fastest known sorting algorithms. These can be called from a VBA module sheet and used to sort VBA arrays on any number of columns with independent sort orders for each of the sortby columns. For sorting by upto 2 columns, use Quicksort facility. For sorting by 3 or more columns use Mergesort facility. For full operating details of this function, please refer to the “Custom Help” provided. You will additionally need to manually establish a link to the AddIn “MyAddin” from within the VBE by going in to Tools -> Browse and selecting the file “MyAddin.xla” before you might be able to use this functionality.

Ends here!


Goodluck and best regards



Deepak Agarwal

---

Opening File Based On Field In Excel
Hi,
I'm trying to open a file based on a date in field G2 and its giving me an error. The date is in the format "MMM DD, YYYY"....The script is below (BTW -- I'm VERY new to VBS, please be nice)

Thank you


Date_to_select = Range("G2")

filename_with_date = "Cycle time report as of " + Date_to_select + ".xls"

Workbooks.Open Filename:= _
"G:EveryoneSiebelRisk TransformationActivity Cycle Time Data" + filename_with_date

---

MS Excel 2000 : Row Selection Based On Criteria
I am using MS Excel 2000.

I need to select a row, delete its contents then hide it, assuming that a particular cell in that row has a specific String value.

All the cells in the used range from "A2" and onwards (A2, A3, A4 etc) have the value "PICK". I want to check the cell in Column "B" for the value "CONTAINER" and then if the cell does contain that value, delete the contents of the entire row and hide the row.

I need to do this for every cell in the used range.

This will automate a task that is cirrently very tedious.

Your help is greatly appreciated.

---

How To Update Output Based On Option Buttons Is Excel?
Hi,

This is really basic but I was wondering about the simplest way to do this.

For the simplicity, I have four option buttons in Excel and one label box. I would like the label box to display a unique number depending on which option button is selected.

I am unable to get a real-time update of the label box. I would like the output to change everytime I select another option button.

Any help is greatly appreciated. Thanks


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

---

Auto-Update Website Based On Excel Worksheet PLEASE HELP!
I'm wondering if I can automatically update a certain page on a website with information from an excel document. The excel worksheet has information that updates automatically(got this part done), and I want the page on the website to update with the information from the excel worksheet every 10 seconds. How can I do this?

---

Excel Cell
Hi ,
if i have this in one of the cells in excel

my name is king

is there a way to automatically put underscores so that we get an output like this

my_name_is_king

pls confirm > do we need to write any macro or any other automated way ????
thanks

---

Excel Cell Value Into VB
I know very little about VB and have scraped together a very effective solution (from the internet) to meet my Excel needs. But,...
In the code below, rather than hard coding the string "C:My Docs etc", I want to be able to use a cell value which has this text

Here is the beginning of the Sub

Dim p As String, x As Variant

p = "C:My DocumentsBradlah blah blah"
x = GetFileList(p)

Would appreciate the help
Thanks

---

Last Cell In Excel
hi this is the code i use to find the last cell row in an xcel sheet.. when i use this code, the err msg displayed is
Object doesn't support this property or method: 'specialcells'
i tried using objExcelApp.range.activecell.specialcells(xlCellTypeLastCell).row. it too didn't work.. pls help me.. but this worked fine as a excel macro

<html>

<head>
<%
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open "d: estexcel est11.xls",,True
response.write objExcelApp.activecell.specialcells(xlCellTypeLastCell).row

%>
</head>

<body>

</body>

</html>

---

How To Get The Last Cell From Excel In Vb?
How can i determine which is the last cell in an excel column through VB code?

---

Cell Value Based On Cell Range
I am trying to have the value of cell A multiplied by a certain amount in Cell B, based on a value range of cell C. Any help would be appreciated.

Ex:

Cell A = 25
Cell B = the value of Cell A multiplied by 10(if the value of Cell C is less than 18), multiplied by 15(if the value of Cell C is greater than or equal to 18), or multiplied by 20(if the value of Cell C is is greater than 20)
Cell C = percentage value (IE. 20.00%)

---

Getting Cell That Has Mix Font In Excel
Hi all

When I try to find some text in excel with some specific font format, it return me the whole cell range, even if this cell has two fonts. And more badly it can not find those cells that has that specific font but the starting text of the cell has different font.

The only solution that I have, to scan each cell character by character and find the required specific format. But this solution is very slow. Can you please suggest some better solution?

Best Regards
Tola

---

How Do You Refer To A Cell In Excel In VB.
Hey all!

I have VERY slim knowledge of VB, but would like to know how to solve this problem.

If MoneyAdding!B1 = "Offerings" Then
'Macro name follows
Offerings_macro

ElseIf MoneyAdding!B1 = "Gift Aided giving" Then
GiftAidedgiving_macro

ElseIf MoneyAdding!B1 = "Gifts" Then
Gifts_macro

Else
PaperRecycling_macro
End If

I'm guessing the MoneyAdding!B1 = "NAME" part is wrong as its what I would write in excel.

Basically I dunno if I wrote my If... Then.. Else correctly. But I want to click a command button on a userform and if the word Offerings is in the Cell B1 on the worksheet MoneyAdding then it will run my macro. Or if it is any of the other names it runs that macro.

BE very greatful for help. How would this be written?
Andy

---

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?

---