Home » Blogs » cheetahs10's blog

Excel Help - VBA

Submitted by cheetahs10 on 15 November, 2011 - 08:21

Help please !!! I have a excel work book with the following sheets , E-1 and Overdue.

The E-1 sheet consist of over a 1000 rows. I have a lot of formulas on that sheet to make certain items in colour and so on.

I need a formula or VBA help to get only the overdue orders on the E-1 sheet and copy them to the overdue sheet.

If the column AC is between -1 and -1000 it should copy that row to the overdue sheet.

If this formula is possible can we put in that column AC should be sorted from Highest to lowest.

On VBA when i enter a code like the on above should it be entered into which sheet and after i am complete with the code how do i save it and use it?

Thanks
cheetah10

»

Excel ms access database query

Submitted by Anonymous on 26 December, 2011 - 17:33.

I want to export and import data from MS access to excel 2010. Please help me out with the code.

»
Nick's picture

Take a look at our brand

Submitted by Nick on 26 December, 2011 - 22:32.

Take a look at our brand new call logging system

»
Vishesh's picture

Put the following code in a

Submitted by Vishesh on 16 November, 2011 - 15:18.
Put the following code in a general module and run. 
Sub CopyRange()
   Dim rngAll     As Range
   Dim wksFrom    As Worksheet
   Dim wksTgt     As Worksheet
 
   Set wksFrom = ThisWorkbook.Worksheets("E-1")
   Set wksTgt = ThisWorkbook.Worksheets("Overdue")
 
   Set rngAll = wksFrom.UsedRange
 
   If wksFrom.AutoFilterMode = True Then wksFrom.AutoFilterMode = False
 
   rngAll.AutoFilter Field:=wksFrom.Range("AC1").Column, Criteria1:=">=-1000", Operator:=xlAnd, _
       Criteria2:="<=-1"
 
   rngAll.SpecialCells(xlCellTypeVisible).Copy wksTgt.Range("A1")
 
   If wksFrom.AutoFilterMode = True Then wksFrom.AutoFilterMode = False
 
   Set rngAll = Nothing
   Set wksFrom = Nothing
   Set wksTgt = Nothing
End Sub
»

VBA Help

Submitted by cheetahs10 on 17 November, 2011 - 05:26.

Hi Vishesh

Thanks for your reply and help. Just one question where should i put the code on the complete workbook or only the e-1 sheet.

In you code the Set wksFrom = ThisWorkbook Worksheets(E-1)

It gives me an error message when I type this in and should i add the E-1 part or not

Thank you

Wesley

»
Vishesh's picture

E-1 should be within ""It

Submitted by Vishesh on 17 November, 2011 - 07:56.

You can put this code in any module. However, it should ideally be in general module.

E-1 should be within ""

It would be better if you provide your xl file as attacment.

»

Help - Excell

Submitted by Anonymous on 6 December, 2011 - 10:06.

Hi thanks for your previous help it really worked nice.

I know want to know if you can help me with another excel problem. Either with conditional formatting or with visual basics.

I have 2 coloumn namely U8 & V8. I want to entire row to highlight in red if when the following happens.
If there is an E in cell U8 and cell V8 is empty than it needs to highlight that row. But it needs to work in with the conditional formatting rules which I already added to change rows to colors when a certain cell has a certain value.

Thank you

»
Vishesh's picture

Conditional formatting - Use

Submitted by Vishesh on 7 December, 2011 - 04:09.

Conditional formatting
- Use a formula to determine which cells to format
- In the formula box provided enter
=AND($U8="E",$V8="")
- Click OK
- In the applies to Box ent
=$8:$8
- Click OK

To copy this formatting on other rows copy entire row and paste

»