Excel Excel VBA Help

[rachelsmith]

Hey,

I am new to Excel VBA. I have a problem as below:

I need to copy data from one excel sheet to another based on certain conditions.
If the values in each column match a particular criteria, I need to move it to another sheet.
For example,column 1 can have values A B C D and E and if the value in column 1=A or B, I need to move those entries to another sheet.
This way I have to check each column and move the entries which meet the criteria to another sheet.

Could somebody please help me?

 

[Flona2012]

Well,,
lets assume that you have the data in Sheet1 and you want to move it to Sheet2 and lets assume that condition is to match Number (1)
and that data in sheet1 is from column A --> E (5 Columns)

****************************************************************************************

Sheets("Sheet2").Cells.Range("A:E").Clear ' To Clear the Range in Sheet2
Dim nSheet1RowNumber,nSheet1ColumnNumber as integer
Dim nSheet2RowNumber,nSheet2ColumnNumber as integer
nSheet1RowNumber=1 ' Here you but number of first row that contain the data
nSheet1ColumnNumber=1 ' Here you but number of first Column that contain the data

nSheet2RowNumber=1 ' Here you but number of first row that the data is moved to
nSheet2ColumnNumber=1 ' Here you but number of first Column that the data is moved to

While Sheets("Sheet1").Cells(nSheet1RowNumber, nSheet1ColumnNumber) <> "" 'To make sure it has data
if Sheets("Sheet1").Cells(nSheet1RowNumber, nSheet1ColumnNumber) =1 then
Sheets("Sheet2").Cells(nSheet2RowNumber, nSheet2ColumnNumber) =Sheets("Sheet1").Cells(nSheet1RowNumber, nSheet1ColumnNumber)
if nSheet2ColumnNumber=5 then
nSheet2RowNumber=nSheet2RowNumber+1 'To get the next line
nSheet2ColumnNumber=1 'To Return to first column
else
nSheet2ColumnNumber=nSheet2ColumnNumber+1 'To Increase column in same row
end if
end if

if nSheet1ColumnNumber=5 then
nSheet1RowNumber=nSheet1RowNumber+1 'To read the next line
nSheet1ColumnNumber=1 'To Return to first column
else
nSheet1ColumnNumber=nSheet1ColumnNumber+1 'To read next column in same row
end if
wend
***********************************************************************************
Now this should be in button event code

if you know what i mean

if you have any further questions, please ask

 

[durue]

I am trying to create a macro (see below) to run a formula. The formula works independent of the macro but not in the macro. I can't figure out what I'm doing wrong.

Sub enter_formulas()
Range("p2").Formula = "=RIGHT(LEFT(Q7,FIND(" ",Q7)-1),10)"
End Sub

 

[davesexcel]

I am trying to create a macro (see below) to run a formula. The formula works independent of the macro but not in the macro. I can't figure out what I'm doing wrong.

Sub enter_formulas()
Range("p2").Formula = "=RIGHT(LEFT(Q7,FIND(" ",Q7)-1),10)"
End Sub

Why would you start this thread in somebody else's thread?

Hit your macro recorder.
Select the cell with the formula and go into the formula bar, then hit enter and stop recording, go into VbA and look at the code.

 

[davesexcel]

Hey,

I am new to Excel VBA. I have a problem as below:

I need to copy data from one excel sheet to another based on certain conditions.
If the values in each column match a particular criteria, I need to move it to another sheet.
For example,column 1 can have values A B C D and E and if the value in column 1=A or B, I need to move those entries to another sheet.
This way I have to check each column and move the entries which meet the criteria to another sheet.

Could somebody please help me?

Attach a sample workbook