Posts by codepro

    Re: Nested Looping referencing multiple variables VBA


    hi Carim,


    Just tried and getting error.



    Function ColLetter(lngCol As Long) As String - as ambiguous name error


    would that be because we are giving "as string" ? Thank you!!!



    Re: Nested Looping referencing multiple variables VBA


    hi Carim,


    I would hate to bug you after your helped me so much. I am trying to round the amount to 2 decimals on the code line for amount and its giving me errors.


    Sheets("test").Range("J" & lrdj + 1) = "=Round(Comm!" & "ColLetter1(j) & i, 2) - I think reason as it ColLetter is specified as string and not number. Would you know how I fix it quickly? if this is a complete modification of the code, I will find another way around it. I just wanted to ask you if this is a quick fix and I am very close to fixing it.


    Thank you so much!



    Re: Nested Looping referencing multiple variables VBA


    wwowow!!!!


    Thank you so so so much for your help!!! it works! how do I learn all of these? I love VBA magic and I want to learn. What would you advice on reading up?


    Re: Nested Looping referencing multiple variables VBA


    Hi Carim,


    The code works great for amounts but not for Comm codes as they still appear hardcopied. Would you be able to help me with it? and after that it would done! Could you please also help me to learn why did you use function and explain what is code "function" is doing?


    Sheets("CSV").Range("I" & lrdi + 1 & ":I" & lrdi + lrs - 1) = Right(Sheets("Comm").Cells(1, j), 3) - code is for hardcoding not referencing, I tried to changing to ="=Right(Comm E1, 3) and it didn't work.




    Re: Nested Looping referencing multiple variables VBA


    Hi Carim!!


    This is fantastic! you are so good!
    The only thing it values show up hardcopied not as a reference.


    Sheets("CSV").Range("J" & lrdj + 1) = Sheets("Comm").Cells(i, j) - hardcopies not as reference


    Sheets("CSV macro").Range("U" & i + (149 * j)) = "=Comm!D" & I - below code line shows as reference. I tried adding ="= to the code above but its giving an error statement is wrong.


    Otherwise, code does magic. just need to reference instead of hardcopied. Would you know how to fix that? thank you so much!






    Re: Nested Looping referencing multiple variables VBA


    hi Carim,


    Thank you so much for your efforts to help me with this! I couldn't upload the excel file so attached the image of what I want. In the attached example, I have 6 rows in Sheet Comm in my current project 150 rows. Also, in sample I have three communities and in my current is 67. I hope that helps with picturing what I am trying to achieve. So sorry that I could not attach the excel spreadsheet. Thank you! [ATTACH=CONFIG]72068[/ATTACH]



    Re: Nested Looping referencing multiple variables VBA


    Hi Carim,


    Thank you so much for your help! Unfortunately, code doesn't run. I tried running it step by step F8, it populates the value of Column E1 only without changing it to F1 after its done looping 150 times. Also, looks like code populates Comm E1 as value not as as a reference (BCP instead of =CommE1). The source data is dynamic that is why I need to keep it as reference not hard coded value.





    Hi All,


    I was wondering if you could help with my code. I am trying to reference Column E1 in Sheet Comm ( source) to Column I2 in Sheet CSV ( destination) 150 times. Once it is done populating the first round, I want now Column F1 in Sheet Comm ( source) to Column I151 in Sheet CSV ( destination) 150 times. The code runs till the first empty cell in row 1 Sheet Comm ( source). there are 67 values that i would like to reference in Sheet CSV in column I.


    what works in my code:
    - Sheets("CSV").Range("I" & r).Value = "=right(Comm!e1,5)" ' paste community code to the first 150 cells in column I -
    what doesn't work:
    - how do I make it change to F1 after it completes the first round of looping?


    Thank you for all your help!



    Re: VBA Repeat Code Variable times


    Thank you so much Carim!!! it works!


    Re: VBA Repeat Code Variable times


    Hi Carim,


    Thank you so much for your response. The source sheet only has value in rows until row 200 after that it is empty. That is why I need to repeat it in destination sheet 50 times once it done with first round.
    I also thought of another way instead of writing macro. I also did on cell K201 = K2 and drag it down all way till row 10000 row. But It would be good to know if there is a macro to do that faster. Thank you! [ATTACH=CONFIG]72049[/ATTACH]



    Hi All,


    I was wondering if you could help me to copy the code down 50 times. I am trying to reference column A in Sheet(Comm) to Sheet(CSV) Column K. I have code so far that is performing just that. But I would like it to repeat 50 times not copying but referencing it. So once it done referencing one batch it will start on cell K201 (Sheet CSV) referencing = Comm!RC[-10] all over again till A200 in sheetComm.
    Result - repeat the code 50 times in same column K. I am trying to build a dynamic formula. The idea each time the source sheet (Comm) changes the Sheet (csv) will get updated for it. Thank you for your help!


    Code
    Sub Macro3()
    Sheets("CSV").Range("k2") = "=Comm!RC[-10]"
    Range("K2").AutoFill Destination:=Range("K2:K200")
    Sub End

    Re: Copy only nonempty cells from Column A to Column B VBA


    Thank you so much!!!! it works great!


    Quote from MrRedli;784563

    try below code:


    Hi VBA experts!


    I am trying to write a code that will only copy non blank values from Column L to Column J. Column L has empty cells that I don't want to copy over Column J since there is formula in Column J in corresponding cells that I would like to keep. My code below isn't working. I tried adding paste as values but keep getting error. Could you please help me? Thank you!



    Code
    Sub LoopExample()
        Dim Rws As Long, rng As Range, c As Range
        Rws = Cells(Rows.Count, "L").End(xlUp).Row
        Set rng = Range(Cells(1, "L"), Cells(Rws, "L"))
        For Each c In rng.Cells
            If c <> "" Then
                c.Cut Cells(Rows.Count, "J").End(xlUp).PasteSpecial  'getting error 
            End If
        Next c
    End Sub

    Hi All,


    I am trying to write a code that will change the value in Column I if Column K has value "Orange" to "Fruit". But I also want the value of the next cell down in Column I change to "Fruit" as well.
    My macro below only works when I have one singular value change but not multiple. Could you please help?
    For example: desired result is if Column K3 = "Orange" then Column I3 & I4 = "Fruit". I have about 300 lines of data to go through. Thank you for your help!!!!


    Re: Change value of two cells based on one cell VBA


    Quote from KjBox;784512

    First remove the colour and bold text from the code you pasted and the quotation marks, then highlight all the code and click the # icon in the menu bar at the top of the Reply window.


    thank you for your help! but couldn't remove the green color from the code. I tried multiple times and I couldn't remove it. Thank you!

    Re: Change value of two cells based on one cell VBA


    Re: Change value of two cells based on one cell VBA


    Quote from KjBox;784485

    codepro


    You have posted enough times on this forum to know that code must be wrapped in code tags. Also do not format anything in the code to a different font colour or make any font bold (including code comments).


    Please edit your post to do the above, thank you.


    how do I do that? how do I put quotation marks?

    Re: Change value of two cells based on one cell VBA


    Hi All,


    I am trying to write a code that will change the value in Column I if Column K has value "Orange" to "Fruit". But I also want the value of the next cell down in Column I change to "Fruit" as well.
    My macro below only works when I have one singular value change but not multiple. Could you please help?
    For example: desired result is if Column K3 = "Orange" then Column I3 & I4 = "Fruit". I have about 300 lines of data to go through. Thank you for your help!!!!


    Hi VBA experts,
    How to copy and paste only non-blank cells from Column A to column B.
    For ex: Copy only non-blank values from column B to Column A and not overriding content of column A. Just copy non-blank cells only without copying blanks to Column A too. Thank you for your help!




    [TABLE="width: 265"]

    [tr]


    [td]

    Column A

    [/td]


    [td]

    Column B

    [/td]


    [td]

    Desired

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]0.708742515[/TD]
    [TD="align: right"]0.71[/TD]
    [TD="align: right"]0.71[/TD]

    [/tr]


    [tr]


    [TD="align: right"]70.91555129[/TD]
    [TD="align: right"]70.92[/TD]
    [TD="align: right"]70.92[/TD]

    [/tr]


    [tr]


    [TD="align: right"]32.87168862[/TD]
    [TD="align: right"]32.87[/TD]
    [TD="align: right"]32.87[/TD]

    [/tr]


    [tr]


    [TD="align: right"]0.351648703[/TD]
    [TD="align: right"]0.35[/TD]
    [TD="align: right"]0.35[/TD]

    [/tr]


    [tr]


    [TD="align: right"]6.783025948[/TD]
    [TD="align: right"]6.78[/TD]
    [TD="align: right"]6.78[/TD]

    [/tr]


    [tr]


    [TD="align: right"]-111.6306571[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]-111.6307[/TD]

    [/tr]


    [tr]


    [TD="align: right"]111.6306571[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]111.6307[/TD]

    [/tr]


    [tr]


    [TD="align: right"]-111.6306571[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]-111.6307[/TD]

    [/tr]


    [/TABLE]

    Hi All,


    I am trying to write a code that will change the value in Column I if Column K has value "Orange" to "Fruit". But I also want the value of the next cell down in Column I change to "Fruit" as well.
    My macro below only works when I have one singular value change but not multiple. Could you please help?
    For example: desired result is if Column K3 = "Orange" then Column I3 & I4 = "Fruit". I have about 300 lines of data to go through. Thank you for your help!!!!