Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 9th, 2003, 10:11 AM
Registered User
 
Join Date: Sep 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Mark_Harrison
Default searching through a sheeft for minus figures

Hi,

I was wondering if anyone can help me I am a fairly new user to VB excel and I hav designed a stock control sheet where I have ran some macros, so that when a assembled productr is sent out a macro removes the items from the stock. I have also put in a shedule so I know how much stock is required for the next 3 months

e.g. column n - total sock column o-stock after month 1

what need is some coding that searches the sheet for the minus numbers in the stock column and copys them to a new sheet can anyone help me

thanks :)

 
Old September 11th, 2003, 03:34 PM
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Mark_Harrison
 Hi,

I was wondering if anyone can help me I am a fairly new user to VB excel and I hav designed a stock control sheet where I have ran some macros, so that when a assembled productr is sent out a macro removes the items from the stock. I have also put in a shedule so I know how much stock is required for the next 3 months

e.g. column n - total sock column o-stock after month 1

what need is some coding that searches the sheet for the minus numbers in the stock column and copys them to a new sheet can anyone help me

thanks :)

Regards,
Kamal
 
Old September 11th, 2003, 03:39 PM
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
 Sorry about the previous post. The following sample code might help you. You can specify the range and destination sheet according
 to your needs.

  Dim cell As Range
  Dim I As Integer,sourceCol As Integer, sourceStartRow As Integer, sourceEndRow As Integer
  Dim destSheetIndex
  sourceCol = 2
  sourceStartRow = 5
  sourceEndRow = 10
  destSheetIndex = 2
  For Each cell In Range(Cells(sourceStartRow, sourceCol), Cells(sourceEndRow, sourceCol))
        If cell.Value < 0 Then
            I = I + 1
            cell.Copy Destination:=Worksheets(destSheetIndex).Cells(I, 1)
        End If
  Next

 If you are using the named range, then you can specify something like this

 "For Each cell In Range("myRange").Columns(colToSearch)" instead of

  "For Each cell In Range(Cells(sourceStartRow, sourceCol), Cells(sourceEndRow, sourceCol))"


Quote:
quote:Originally posted by KamalasekarR
 
Quote:
quote:Originally posted by Mark_Harrison
Quote:
 Hi,

I was wondering if anyone can help me I am a fairly new user to VB excel and I hav designed a stock control sheet where I have ran some macros, so that when a assembled productr is sent out a macro removes the items from the stock. I have also put in a shedule so I know how much stock is required for the next 3 months

e.g. column n - total sock column o-stock after month 1

what need is some coding that searches the sheet for the minus numbers in the stock column and copys them to a new sheet can anyone help me

thanks :)

Regards,
Kamal
Regards,
Kamal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Numerals into figures renu2214 Classic ASP Professional 0 April 17th, 2007 09:34 AM
MINUS / EXCEPT in MS Access d-fxt Access 2 March 8th, 2007 06:09 PM
Comparing the figures in two columns on each sheet sibo32000 Excel VBA 3 February 26th, 2007 01:29 PM
Calculate Workdays minus holidays woodan Access 1 February 11th, 2004 04:17 PM
Minus Key in Datagrid whammoed VB.NET 0 September 29th, 2003 09:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.