I'm trying to create a loop to read elements from column B (the total number
changes all the time) and based on their value, copy and paste different
formulas stored in other cells i.e.(E:81,F:81) to the column next to the
element:

B C D
Li Formula 1
Na 5 Formula 2
Sc Formula 1
Co Formula 3
Ni 3 Formula 2
Cu Formula 1
Mo 2 Formula 2

The elements Li, Sc, Rh, Ho use the same Formula 1, element Kr uses a unique
formula (Formula 2), the elements with numbers in column C use Formula 3 and
finally the rest of elements with empy spaces in C get Formula 4.

Any help will be greatly appreciated!

Hi
what formulas do you use exactly. Maybe an IF statement would be
sufficient

--
Regards
Frank Kabel
Frankfurt, Germany

"gaba" schrieb im Newsbeitrag
...
I'm trying to create a loop to read elements from column B (the
total number
changes all the time) and based on their value, copy and paste
different
formulas stored in other cells i.e.(E:81,F:81) to the column next to
the
element:

B C D
Li Formula 1
Na 5 Formula 2
Sc Formula 1
Co Formula 3
Ni 3 Formula 2
Cu Formula 1
Mo 2 Formula 2

The elements Li, Sc, Rh, Ho use the same Formula 1, element Kr uses a
unique
formula (Formula 2), the elements with numbers in column C use
Formula 3 and
finally the rest of elements with empy spaces in C get Formula 4.

Any help will be greatly appreciated!

Hi Frank,

For Li, Sc, Rh and Ho
Set Offset(0,3).Value = OFFSET($B$86,12+ROW($E1),2,1,1)
Set Offset(0,4).Value = =OFFSET($B$86,(16+2*$F$6)*F$8+12+ROW($E1),2,1,1)/$E16

For Kr
Set Offset(0,3).Value =OFFSET($B$86,12+ROW($E26),2,1,1)
Set Offset(0,4).Value =OFFSET($B$86,(16+2*$F$6)*F$8+12+ROW($E26),2,1,1)

For < Li, Sc, Rh, Ho or Kr
Set Offset(0,3).Value
=IF(OFFSET($B$86,(12+ROW($E2)),3,1,1)=$D17,OFFSET ($B$86,(12+ROW($E2)),3,1,1),CONCATENATE("< ",$D17))

Set Offset(0,4).Value
=IF(OFFSET($B$86,(16+2*$F$6)*F$8+12+ROW($E2),3,1,1 )=$D17,OFFSET($B$86,(16+2*$F$6)*F$8+12+ROW($E2),3 ,1,1)/F$9*F$10*F$11/F$12,CONCATENATE("<
",ROUND($D17/F$9*F$10*F$11/F$12,3)))


Since I don't know which row the elements are going to be (their position
changes each time data is imported) right now the copy and paste from these
(E81:F:84) cells is doing the job.

I hope all these is not too confusing. I've been trying so hard to keep it
simple that I got lost in the "loop"

Thanks a lot

"Frank Kabel" wrote:

Hi
what formulas do you use exactly. Maybe an IF statement would be
sufficient

--
Regards
Frank Kabel
Frankfurt, Germany

"gaba" schrieb im Newsbeitrag
...
I'm trying to create a loop to read elements from column B (the
total number
changes all the time) and based on their value, copy and paste
different
formulas stored in other cells i.e.(E:81,F:81) to the column next to
the
element:

B C D
Li Formula 1
Na 5 Formula 2
Sc Formula 1
Co Formula 3
Ni 3 Formula 2
Cu Formula 1
Mo 2 Formula 2

The elements Li, Sc, Rh, Ho use the same Formula 1, element Kr uses a
unique
formula (Formula 2), the elements with numbers in column C use
Formula 3 and
finally the rest of elements with empy spaces in C get Formula 4.

Any help will be greatly appreciated!

Hi there,
Well...I'm trying hard and the code is kind of doing what I'm looking for...
still needs some work. (I'm thankful for this Discussion Group, great source
for learning/improving skills) :)

I need some help in grouping the conditions
Also I'm having problems with the one I'm looking for two different values
in two different columns (AND?)
Then I'll set the original formula to increment the row and I'll be done

Sub SetFormulas()
'loop thru elements and conditions to copy and paste different formulas
stored at Range("E81","F84")
'If element = Li, Sc, Rh or Ho, copy formula with green background
Range("E81","F81")
'If element = Kr, copy formula with purple background Range("E84","F84")
'If element < Li, Sc, Rh, Ho or Kr, and column d is not empty, copy formula
with yellow background Range("E82","F82")
'else copy formula with clear background Range("E83","F83")

Dim e As Range

Sheets("ppb data").Range("B16").Select
Do
If ActiveCell.Offset(0, 0).Value = "Li" Then
Range("E81", "F81").Copy Destination:=ActiveCell.Offset(0, 3)

ElseIf ActiveCell.Offset(0, 0).Value = "Sc" Then
Range("E81", "F81").Copy Destination:=ActiveCell.Offset(0, 3)

ElseIf ActiveCell.Offset(0, 0).Value = "Rh" Then
Range("E81", "F81").Copy Destination:=ActiveCell.Offset(0, 3)

ElseIf ActiveCell.Offset(0, 0).Value = "Ho" Then
Range("E81", "F81").Copy Destination:=ActiveCell.Offset(0, 3)

ElseIf ActiveCell.Offset(0, 0).Value = "Kr" Then
Range("E84", "F84").Copy Destination:=ActiveCell.Offset(0, 3)

ElseIf ActiveCell.Offset(0, 0).Value < "Li" < "Sc" < "Rh" < "Ho"
< "Kr" Then
'And (ActiveCell.Ofsset(0, 2) 0)
Range("E82", "F82").Copy Destination:=ActiveCell.Offset(0, 3)

Else
Range("E83", "F83").Copy Destination:=ActiveCell.Offset(0, 3)
End If

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))

End Sub

Thanks so much for any help!
Gaba

I got it working. Just in case somebody else need it...

Sub SetElements()
'method ids contains the data needed
'ppb data contains the value(s) (DestChk)I use to look up the value I need
(SrcFnd)
'if DestChk is true, then the value is returned otherwise Null (0) is entered

Dim MethRange As Range, SrcChk1 As Range
Dim SrcFnd1 As String, SrcFnd2 As String, DestChk1 As String
Dim DestChk2 As String

Set MethRange = Sheets("Method Ids").Range("K3:K200")

Sheets("ppb " & myfilename & " data").Range("B16").Select
Do
DestChk1 = ActiveCell.Offset(0, 0).Value
DestChk2 = ActiveCell.Offset(0, 1).Value
Set SrcChk1 = MethRange.Find(what:=DestChk1, lookat:=xlWhole, _
SearchOrder:=xlByColumns)

If Not SrcChk1 Is Nothing Then
SrcFnd1 = SrcChk1.Offset(0, -10).Value

If DestChk2 = SrcChk1.Offset(0, 1).Value Then
SrcFnd2 = SrcChk1.Offset(0, 1).Value
ActiveCell.Offset(0, -1).Value = SrcFnd1
ActiveCell.Offset(0, 2).Value = SrcChk1.Offset(0, 2).Value
End If

Else
ActiveCell.Offset(0, -1).Value = ""

End If

If IsEmpty(ActiveCell) Then
ActiveCell.Value = ""
End If

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Range("E2").Select
End Sub