Paul, Thank you for the answer and simple explanation. I well understood the error and your code.
Don, Thank you for the answer and it's working. Regards, Dilan On Wed, Sep 19, 2012 at 2:49 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > it looks like your first macro is irrelevent to your question. > You're wanting repairs to: > ------------------------------------ > > Private Sub salarynew() > > Dim r As Double > > For r = 1 To 20 > > ' Earlier E now should be value of cell I1 > > ' Earlier F now should be value of cell I2 > > If Cells(r, *"cells(1,"I")"*) = "Single" Then > > Cells(r, *"cells(2,"I")"*) = "8000" > > Else > > If Cells(r, *"cells(1,"I")"*) = "Married" Then > > Cells(r, *"cells(2,"I")"*) = "11000" > > End If > > End If > > Next > > End Sub > > ---------------------------------- > > > > the problem is: when you use "cells(1,"I")" you are EXPLICITLY > defining a string of characters. > > The beginning and ending quotes mean: "do not evaluate the contents, but > use these characters exactly as shown" > > You might as well say "this means nothing", because to the cells(r,c) > method, they mean the same thing. > > the string of characters do not represent a combination letter or numeric > values that represents a column. > > > > You COULD use: > > > > Col_Satus = Cells(1,"I").value > > Col_Salary = Cells(2,"I").value > > > > then use: > > If Cells(r,Col_Status).value = "Single" then > > Cells(r,Col_Salary).value = 8000 > > > > --------------------------- > > BTW: = "8000" is not the same as = 8000 > > > > = "8000" means to set the value of the cell to a text string "8000". > > > > Now, if the format of the cell is set to General, or Number, excel MAY > recognize the value as numeric and CONVERT it to a number. > > But I'd hate to base my salary on it. > > > > *Paul* > > ----------------------------------------- > *“Do all the good you can, > By all the means you can, > In all the ways you can, > In all the places you can, > At all the times you can, > To all the people you can, > As long as ever you can.” - John Wesley > *----------------------------------------- > > > ------------------------------ > *From:* Dilan De Silva <dila...@gmail.com> > *To:* excel-macros@googlegroups.com > *Sent:* Wed, September 19, 2012 5:03:33 AM > *Subject:* $$Excel-Macros$$ Substitute a value of a cell in to a cell > reference of a vba code > > Hi, > > Refer attached sheet(format 1). I want to fill the cells in one > column(Basic Salary) depend on cells value of a another column(Status). It > means if cell E1 has “single” then F1 should be 8000 and if cell E1 has > “Married” then F1 should be 11000.( I don’t want to use formulas) > > I wrote the below code to do that. > > > > Private Sub salary() > > Dim r As Double > > For r = 1 To 20 > > If Cells(r, "E") = "Single" Then > > Cells(r, "F") = "8000" > > Else > > If Cells(r, "E") = "Married" Then > > Cells(r, "F") = "11000" > > End If > > End If > > Next > > End Sub > > > > Now the columns are not always constant. It means Columns for “Status” are > E and D in format1 and format2 respectively. > > I want to use value of cell I1 and I2 for “Status and “Basic salary” > respectively. I will manually enter the column label for cell I1 and I2. > > I wrote below macro, but it is not working. I think the way I referred > the values of cell I1 and I2 is not correct. > > > > Please guild me to write this code perfectly. > > I’m not expert in macro, therefore there may be more easy methods to do > this task. However I want to do on this way since I’m can understand this > types of > > Codes well. > > > > Private Sub salarynew() > > Dim r As Double > > For r = 1 To 20 > > ' Earlier E now should be value of cell I1 > > ' Earlier F now should be value of cell I2 > > If Cells(r, *"cells(1,"I")"*) = "Single" Then > > Cells(r, *"cells(2,"I")"*) = "8000" > > Else > > If Cells(r, *"cells(1,"I")"*) = "Married" Then > > Cells(r, *"cells(2,"I")"*) = "11000" > > End If > > End If > > Next > > End Sub > > > > Thanks, > > Regards, > > Dilan > > > > > > > > > > > > -- > Join official facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES (1120+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > 6) Jobs posting is not allowed. > > 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To post to this group, send email to excel-macros@googlegroups.com. > To unsubscribe from this group, send email to > excel-macros+unsubscr...@googlegroups.com. > > > > -- > Join official facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES (1120+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > 6) Jobs posting is not allowed. > > 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To post to this group, send email to excel-macros@googlegroups.com. > To unsubscribe from this group, send email to > excel-macros+unsubscr...@googlegroups.com. > > > -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
