VBA Matching value in a range and return other cell values

[Jasesair]

I didn't think this would be difficult...but perhaps I'm not thinking clearly.

I have values in cells H8:K8 from sheet 'Data Entry'. In sheet 'Results', I'm wanting to match A1 with A1's unique value in column F (it's long but if a range is needed, then F3:F6000). Once matched, I'd like the H8:K8 'Data Entry' cells to be returned in column H:K in the matched row in the 'Results' sheet. I hope that's making sense - I'm sure I could have explained that far better!!!

Is this possible with a little VBA?

 

[Peter_SSs]

The code will result in "Numbers stored as text". When you manually type a number in one of the cells it will be a normal number.
Do you want

A. The code to to still result in numbers stored as text values but have it so that manually entered numbers are also text?

or

B. The code to change so that it enters actual numbers in the cell then when you manually type, these will be actual numbers too?

 

[Jasesair]

I think the safest option is Option A, knowing that everything functions correctly now.

This is the code that I currently have:

Sub TransposeData()
  Dim Results As Variant
  Dim rngData As Range, rngFound As Range
  Dim lr As Long
  Dim bColsHidden As Boolean
  
  Application.ScreenUpdating = False
  
  Worksheets("Data Entry").Unprotect
  
  With Sheets("Data Entry")
    bColsHidden = .Columns("H").Hidden
    .Columns("H:K").Hidden = False
    On Error Resume Next
    lr = .Range("H:K").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    If lr >= 10 Then
      Set rngData = .Range("H10:K" & lr)
    End If
    .Columns("H:K").Hidden = bColsHidden
  End With
  Application.ScreenUpdating = True
  If rngData Is Nothing Then
    MsgBox "No data in 'Data Entry' H:K from row 10"
  Else
    If rngData.Rows.Count = 1 Then
      Results = Split(Join(Application.Index(rngData.Value, 1, 0), "@"), "@")
    Else
      Results = Split(Join(Application.Transpose(Evaluate( _
              rngData.Columns(1).Address(External:=True) & "&""@""&" & _
              rngData.Columns(2).Address(External:=True) & "&""@""&" & _
              rngData.Columns(3).Address(External:=True) & "&""@""&" & _
              rngData.Columns(4).Address(External:=True))), "@"), "@")
    End If
    With Sheets("Results")
      Set rngFound = .Columns("F").Find(what:=.Range("A1").Value, LookAt:=xlWhole)
      If Not rngFound Is Nothing Then
        rngFound.Offset(, 2).Resize(, UBound(Results) + 1).Value = Results
      Else
        MsgBox .Range("A1").Value & " not found in column F"
      End If
    End With
  End If
  
  Worksheets("Data Entry").Protect
  
End Sub

 

[Peter_SSs]

I think the safest option is Option A, knowing that everything functions correctly now.

Then try changing this section

If Not rngFound Is Nothing Then
  With rngFound.Offset(, 2).Resize(, UBound(Results) + 1)
    .NumberFormat = "@"
    .Value = Results
  End With
Else
  MsgBox .Range("A1").Value & " not found in column F"
End If

 

[Jasesair]

Do I need to change the way I manually enter some of these figures in addition to the code working to transpose figures? I've made this change above but nothing different appears to be happening..

 

[Peter_SSs]

Are you manually entering values in the same row that the code is? That is, the code looks in A1 of 'Results' and then finds that value in column F.
In that found row it formats cells as Text from column H in that row across as far as it needs and then enters the values (as Text).
If you type a number anywhere in that row range it will be entered as a Text value because that is how the cell is now formatted.

If that is not what you want you will have to make it clearer to me.

 

[Jasesair]

You're definitely understanding correctly. It's just not happening for me. The minisheet is attached below:

ARD Performance 210518.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S
3	2	2	2	2	2	2	2	2	1	1	1	1
4	2	3	2	3	3	2	3	2	1	1
5	2	1
6	1	2	2	2			1	2		3	3	2
7	3	3	3	2			1	3		2	2	1
8	2	3	2	1			1	1		1	1	2
9	3	2	1	2			2	2		2	3	3
10	2	1	3	3	2	2
11	1	2	2	3	3	2
Results

Hopefully you can see the little green arrows showing the numbers coming into this Results sheet as text. All the other numbers I've manually added.

EDIT: I can see that the arrows haven't come through with the mini sheet. Using row 3 as the example, the numbers from column H:O were populated with the code, and I entered the numbers in P:S. P:S don't have the arrow stating: "The number in this cell is formatted as text or preceded by an apostrophe."

 

[Peter_SSs]

Hopefully you can see the little green arrows showing the numbers coming into this Results sheet as text.

No, the numbers come across as numbers with no green triangles.

Try this sample file. Jaseair Sample

• After downloading and before you run the code, check cell H6, or any cell to the right of that, on the 'Results' sheet. You will see the cells are formatted as 'General'
• Now run the code and check those cells again. They are formatted as Text for me.
• Then type a number into any of those cells (eg H6) it is still formatted as Text. At least that is what happens for me.

 

[Jasesair]

Just thought I'd share that after leaving this project for a while that I have now returned and fixed this issue. I needed to change the cells in the Results page to being 'text'. All sounds simple but it hasn't worked retrospectively for the manually entered data. Now that it's all 'text', the manual entry works as it should for new manually entered data (I just had to delete all previously entered data). Happy days! Thanks for all your help, Peter.

 

[Peter_SSs]

Glad you have resolved it. Thanks for letting us know.