PDA

View Full Version : Need help copy cell based on other column value



Rafea1979
01-04-2016, 07:26 PM
Hi

I have two columns "A"&"E" column with numbers and I would like to copy cell E only if "A" = 3.

The order of the numbers in column "A" change based on the file.

example

A E
8 6.0
3 8.1
5 9.2
6 9.1

I need VBA code to lookup for 3 in column A then copy E cell.

Any help

Bob Phillips
01-05-2016, 01:29 AM
Copy to where?

Rafea1979
01-05-2016, 01:54 AM
Copy to another cell in the same sheet "D22"

Bob Phillips
01-05-2016, 05:25 AM
What happens if there is more than one that meets the copy criteria?

Rafea1979
01-05-2016, 12:02 PM
The numbers are not repeated.
The numbering is from 1 to 20 but sometimes missing some numbers.
for example
3,4,5,6,7
1,2,3,4,5

The issue is that number 3 sometimes becomes in A7 or A8 or A9 or A10 cells and I need to copy "E" cell based on where is it come.
I have tried to do filter however it copies E cell but with different number.
I need to copy "E" which is the value at "A" = 3 then paste into another cell in the same sheet.

Rafea1979
01-05-2016, 12:15 PM
The numbers are not repeated.
The numbering is from 1 to 20 but sometimes missing some numbers.
for example
3,4,5,6,7
1,2,3,4,5

The issue is that number 3 sometimes becomes in A7 or A8 or A9 or A10 cells and I need to copy "E" cell based on where is it come.
I have tried to do filter however it copies E cell but with different number.
I need to copy "E" which is the value at "A" = 3 then paste into another cell in the same sheet.

It will be more useful if I can unify all files in the order of column "A"
For example always start from 1 then 2 then 3 and so on until 25 keeping the "E" unchanged and add Zero for any cell to add.
The thing is that I have txt files when I process them into excel some order start with 3 and some others start with 1 or 2.
If I can make all file start with 1 then I will be able to copy specific "E" cell with specific number and I do prefer this way.

For example
if i have 2,3,4,5,6,7,8,9,10 then I need to add one "A" cell to become 1,2,3,4,5,7,8,9,10
If i have 3,4,5,6,7,8,9,10 then add "A" cell to get 1,2,3,4,5,6,7,8,9,10
make "E" value for the added cell ZERO.
I will post the code I use to convert txt into excel here.

I really appreciate your help.

Thank you

Rafea1979
01-05-2016, 12:19 PM
HERE IS THE CODE USED TO CONVERT TXT into EXCEL.

I NEED TO ADD CONDITIONAL "A" CELLS TO BE ALWAYS START WITH "1" and "E" value for added cell to be ZERO without changing "E" values.


Sub d_spacing_code()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog


'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
myExtension = "*.txt"


'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

Workbooks.OpenText Filename:=myPath & myFile _


Rows("6:13").Select
Selection.Delete Shift:=xlUp
Range("A1:C5").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = "Pixel Size [um]"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Camera Length [mm]"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Wavelength [A]"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Average d-spacing [A]"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Standard Deviation"
Range("C1").Select
ActiveCell.FormulaR1C1 = "172"
Range("C2").Select
ActiveCell.FormulaR1C1 = "3342.06"
Range("C3").Select
ActiveCell.FormulaR1C1 = "1.0332"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(E8:E16)"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=STDEV.S(E8:E16)"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Order"
Range("B6").Select
ActiveCell.FormulaR1C1 = "# of Pixels"
Range("D6").Select
ActiveCell.FormulaR1C1 = "d [A]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "d*Order"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=R3C3/(2*SIN(0.5*ATAN(RC[-2]*R1C3/(1000*R2C3))))"
Range("D7").Select
Selection.AutoFill Destination:=Range("D7:D" & Range("B" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
Range("D7:D18").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-4]"
Range("E7").Select
Selection.AutoFill Destination:=Range("E7:E" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
Range("E7:E18").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIF(R[4]C[2]:R[12]C[2],""<>0"")"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=STDEV.S(R[3]C[2]:R[10]C[2])"
Range("C6").Select

ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close


'Get next file name
myFile = Dir
Loop


'Message Box when tasks are completed
MsgBox "Task Complete!"


ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Bob Phillips
01-05-2016, 12:44 PM
Now I am totally lost.

Originally, you said you wanted top copy cell E in the row where cell A is 3 over to D22.

Now we have a text file, you are wanting to insert any missing values in A.

As I say, I don't get it.

Post your text file, your workbook, and give us details of expected results.

Rafea1979
01-05-2016, 01:51 PM
Thank you for your reply.

Can you help me to do this

"Originally, you said you wanted top copy cell E in the row where cell A is 3 over to D22"

Just ignore what I said about txt file.