0

I would really appreciate a help here. I have a formula in cell C4 like this one

=INDIRECT("'"&B4&"'!D4")

To get the value of cell D4 in other sheet which name is written in B4

Excel Cells Appearance

enter image description here

Sheet appearance

enter image description here

I realized that sheet has 'space' in it hence the excel formula above. But when i try to put this formula in VBA, it gives me an error. and the formula is highlighted in green.

In VBA

enter image description here

Sheets("Data").Range("C4").Select ActiveCell.Formula = "=INDIRECT(" '"&B4&"'!D4")"

I don't understand it since i'm really an amateur in VBA. What should i do?

Thank you in advance

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
AmateurBrain
  • 1
  • see https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba – teylyn Jul 20 '20 at 04:32

1 Answers1

0

Try below. You have to adjust spaces exactly as sheet name.

Sub SpaceIndir()
    Sheets("Data").Range("C4").Formula = "=INDIRECT(' " & B4 & "'!D4)"
End Sub

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Hi Harun, Thanks for editing and trying to help, i eventually found the way to do it by manually recording a macro and look how the formula is formed in VBA. i Didn't think about this before, but the formula looks like this >"=INDIRECT(""'""&B4&""'!D4"")" Thank you – AmateurBrain Jul 20 '20 at 05:03