Excel VBA pasting fixed value to a range

Question

I am trying to copy paste data from worksheet "Sheet10" in one workbook to DataToPaste.csv Sheet1. I am successful in copying rest of the data, however, in column K1, I need to paste a fixed value from range- K1 to the num of rows of data in other columns. I get the value of num rows in DataToPaste.csv in variable PR, but still I can't figure out how do I come up with the correct MyPasteRange, say "K1:K4" or so. Please advise!

       Sub MyMacro()

        Dim LR As Long, PR As Long, X As Long, C As String
        ThisWorkbook.Activate

        With Sheets("Sheet10")
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
        MyCopyRange = Array("A5:A" & LR, "B5:B" & LR, "C5:C" & LR, "D5:D" & LR, "E5:E" & LR, "F5:F" & LR, "G5:G" & LR, "H5:H" & LR, "I5:I" & LR, "J5:J" & LR, "K5:K" & LR, "M5:M" & LR) 'Put ranges in an array
        MyPasteRange = Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1", "K1", "L1") '"K1" - Fixed value WILL GO HERE


             'open target csv file
             Set myData = Workbooks.Open(strPath & "DataToPaste.csv")
             Worksheets(1).Select
             Sheets("Sheet1").UsedRange.Clear

            If LR > 1 Then
                j = 0
                For X = LBound(MyCopyRange) To UBound(MyCopyRange) 'Loop the array copying and pasting based on element in the array
               .Range(MyCopyRange(j)).Copy

               If j = 10 Then
                  Dim col As String
                  col = ConvertToLetter(j + 1)
                  PR = Sheets("LOB2RG").Range("A" & .Rows.Count).End(xlUp).Row ' num rows in LOB2RG
                  Dim r As Range
                  Set r = Range(col & "1:" & col & PR)     
                  r.Value  = "ABC"
             Else
                  Sheets("Sheet1").Range(MyPasteRange(j)).PasteSpecial xlPasteValuesAndNumberFormats 'xlPasteValues
             End If

                   j = j + 1
                Next

            Else
                Range("A1") = "No Data Found"
            End If
        End With
    End Sub
Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)

   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Sorceri · Accepted Answer · 2018-09-07 20:57:11Z

0

Here is a example of how to add a value to all cells within a range IE K1 to K, in this example K14.

Dim i As Integer
Dim r As Range
i = 10
Set r = Range("K1:K14")
'using PR
'Set r = Range("K1:K" & PR)
'using a dynamic range
'Set r = Range(Cells(1, j), Cells(PR, j))
r.Value = i

edited Sep 7, 2018 at 20:57

answered Sep 7, 2018 at 20:32

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SilverFish Over a year ago

@Soceri: in my code I am using index instead of Col "K". I have j = 9. Please guide how do I convert Set r = Range("K1:K" & PR) to using variable j

SilverFish Over a year ago

Dim r As Range Set r = Range(j & "1:" & j & PR) When I use dynamic values, I get Run-time error '1004': Application-defined or object-defined error

SilverFish Over a year ago

Found the solution online ConvertToLetter function to convert int to number which did the trick. Modified the post to working code

Marcucciboy2 Over a year ago

@SilverFish you could also use this instead of the convert to letter formula Set r = Range(Cells(1, j), Cells(14, j))

Collectives™ on Stack Overflow

Excel VBA pasting fixed value to a range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related